CODEWITHSUNDEEP
mysqlsql
Manoj Ghediya
Manoj Ghediya

Reputation: 17

Fetch records from multiple tables using join

I have three tables such as "tbl_purchase", "tbl_user" & "tbl_contestant". I need all records from tbl_purchase with "name" that has in two tables "tbl_user" & "tbl_contestant".

My Query,

    SELECT tp.*, tu.* 
       FROM tbl_purchase tp
       INNER JOIN tbl_user tu 
       ON tu.user_id = tp.user_id

Table: tbl_purchase

 +-------+-------------+------+
 | p_id  | user_id     | Star |
 +-------+-------------+------+
 | 1     | 1           | 50   |
 | 2     | 4           | 100  |
 | 3     | 6           | 150  |
 +-------+-------------+------+

Table: tbl_user

 +-------+-------------+
 | u_id  | name        |
 +-------+-------------+
 | 1     | Sachin      |
 | 4     | Akshay      |
 +-------+-------------+

Table: tbl_contestant

 +-------+-------+-------------+
 | c_id  | u_id  |  Name       |
 +-------+-------+-------------+
 | 1     |   6   |  Mayank     |
 | 2     |   8   |  Nikhil     |
 | 3     |   9   |  Vipul      |
 +-------+-------+-------------+

I want below result,

 +-------+-------+-------------+------+
 | p_id  | u_id  |  Name       | Star |
 +-------+-------+-------------+------+
 | 1     | 1     |  Sachin     | 50   |
 | 2     | 4     |  Akshay     | 100  |
 | 3     | 6     |  Mayank     | 150  |
 +-------+-------+-------------+------+

Upvotes: 0

Views: 74

Answers (2)

ThataL
ThataL

Reputation: 165

Try this. Because of your name is not in a single table so leftJoin on both user and contestant table. and as @Akina said in comment COALESCE returns first non-null value from the list Reference

select tbl_purchase.p_id, 
tbl_purchase.user_id as u_id, 
COALESCE(tbl_user.name, tbl_contestant.Name) as name 
        from tbl_purchase 
        LEFT JOIN tbl_user ON tbl_user.u_id = tbl_purchase.user_id 
        LEFT JOIN tbl_contestant ON tbl_contestant.u_id = tbl_purchase.user_id

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37029

Try this SQL query:

select p.p_id, u.u_id, u.name, p.star
from tbl_purchase p
inner join (
    select u_id, name from tbl_user
    union
    select u_id, name from tbl_contestant
) u on p.user_id = u.u_id;

Result:

p_id    u_id    name    star
1       1      Sachin   50
2       4      Akshay   100
3       6      Mayank   150

Explanation:

  • Your users are in 2 tables - tbl_user and tbl_contestant
  • So, you have to combine the users using a UNION (which removes duplicates)
  • Then, you can join with your purchase table

Example: https://rextester.com/RKHXL43587

Upvotes: 1

Related Questions