How do I get only a specific record from multiple tables?
I have tables table1 ',' table2 , table3 and table4 . table1 has a column account based on whether I need to get a record from table2 or table3 or table 4 against another user_id column.
I am using a query like
SELECT *
FROM table1
LEFT OUTER JOIN table2 ON table1.user_id = table2.user_id
LEFT OUTER JOIN table2 ON table1.user_id = table2.user_id
LEFT OUTER JOIN table3 ON table1.user_id = table3.user_id
using this I am getting a lot of NULL values because each record of table1 will only have a record in the table "table2" or "table3" or "table4".
The current output is like -
t1.user_id | t1.post_id | t2.token | t3.token | t4.token
1 | 1 | xyz | NULL | NULL
1 | 1 | NULL | abc | NULL
1 | 1 | NULL | NULL | etc
required as
t1.user_id | t1.post_id | token
1 | 1 | xyz
1 | 1 | abc
1 | 1 | etc
t2, t3 and t4 can have more than 1 column to choose from.
+3
user3291673
source
to share
3 answers
It would be much easier to use a function COALESCE()
:
SELECT
t1.user_id,
t1.post_id,
COALESCE(t2.Token, t3.Token, t4.Token) AS Token
FROM table1
LEFT OUTER JOIN table2 ON table1.user_id = table2.user_id
LEFT OUTER JOIN table2 ON table1.user_id = table2.user_id
LEFT OUTER JOIN table3 ON table1.user_id = table3.user_id
+1
source to share
You can do it:
SELECT
t1.user_id,
t1.post_id,
MAX(t2.token) AS token2,
MAX(t3.token) AS token3,
MAX(t4.token) AS token4
FROM table1 AS t1
LEFT OUTER JOIN table2 AS t2 ON t1.user_id = t2.user_id
LEFT OUTER JOIN table3 AS t3 ON t1.user_id = t3.user_id
LEFT OUTER JOIN table4 AS t4 ON t1.user_id = t4.user_id
GROUP BY t1.user_id, t1.post_id
0
source to share