I am currently trying to join two tables. This is what their skeleton looks like

TABLE #1:

TABLE #2:

I want to get T2D2 and T2D3 and have the table have the max id from table 1 for all the rows. It should look something like this:

Desired Result:

I think I am over thinking this problem, I have tried cross join and different select statements.

This is where I am currently:

SELECT max(t1.id) inv_id, t2.t2d2, t2.d3 FROM t_name2 t2 CROSS JOIN t_name t1"

There's a case statement with data from table 2, but I have tested the select statement from table 2 by itself and it is gathering the correct information. I am simply trying to copy the max id from table #1 to all the rows of the resulting table from table #2.

I have also tried something along the lines of:

SELECT max(t1.id) inv_id, t2.t2d2 from t_name t1, t_name2 t2 where t2.t2d2 in ('Black', 'Blue')

But it still fails to join. The syntax is incorrect and there's nothing to match both of the tables, so I am having difficulty here.

I have had success with a statement like this:

SELECT 46, t2.t2d2 from t_name2 t2 where t2.t2d2 in ('Black', 'Blue') But that is a manual input. I'd rather do this all in one query, but it looks like I might have to query for the max id first and then add it in the fashion above.

I have not been able to find a similar question. (If it has already been asked feel free to redirect me.)

Thank you for taking the time to read and respond to this post.

CodePudding user response：

On the face of it, it just seems you need a sub-query, but I have a feeling you have oversimplified or missed something out.

SELECT (SELECT MAX(t1.id) FROM t_name t1) inv_id, t2.t2d2, t2.d3
FROM t_name2 t2