Home > OS >  Max Value from one table cross joined to select values from another table
Max Value from one table cross joined to select values from another table

Time:10-11

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

TABLE #1:

ID T1DATA1 T1DATA2
46 ... ...
47 ... ...
48 ... ...

TABLE #2:

ID T2D2 T2D3 T2D4
1 Black Mohawk 745
2 Blue Flat-top 746
3 Yellow Hunger 747

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:

END_ID END_D2 END_D3
48 Black Mohawk
48 Blue Flat-top
48 Yellow Hunger

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
  • Related