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