How to get only specific record from multiple tables?

3
votes

I have tables ‘table1', ‘table2’,’table3' and 'table4’ . ’table1’ has a column ‘account’ on basis of I need to get record from 'table2’ or ‘table3’ or ‘table4’ with respect of another column ‘user_id’.

I am using 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 lots of NULL values because each record of table1 will have only record in either ‘table2’ or ‘table3’ or ‘table4’.

Present 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

needed is like

 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 select.

4
mysqlsqldatabasequery-optimization
can you provide a sample output also so i can amend my answer further – Matt
Thank you @matt.Present 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 will be like t1.user_id | t1.post_id | token 1 | 1 | xyz 1 | 1 | abc 1 | 1 | etc – user3291673
can you put it in the original post please so it is formatted correctly – Matt

4 Answers

1
votes

It would be much simpler to use the COALESCE() function:

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
votes

For this you can use COALESCE() function.

The COALESCE() function returns the first non-null value in a list. for e.g. 

select COALESCE(NULL, NULL, NULL, 'abc', NULL, 'xyz');

the result of the above query is abc.

Updated query :

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
0
votes
SELECT * 
FROM table1 t1
INNER JOIN table2 t2 ON t1.user_id = t2.user_id
INNER JOIN table3 t3 ON t1.user_id = t3.user_id
INNER JOIN table4 t4 ON t1.user_id = t4.user_id
0
votes

You can do this:

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