Fetch records from multiple tables using join

Question

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  |
 +-------+-------+-------------+------+

@ManojGhediya can you add queries that you tried so far? If you can add some queries you tried, someone can guide you appropriately. — zedfoxus
– zedfoxus, Commented Mar 19, 2020 at 5:38
LEFT JOIN both slave tables and use COALESCE() for to find the name. Decide what name must be returned if the records from slave tables contains the same user id with different names. — Akina
– Akina, Commented Mar 19, 2020 at 5:41
@ManojGhediya you have asked a question on stackoverflow before also and have not accepted an answer for the previous question. If you find answers helpful, please go ahead and mark one of the response as an answer so as to put closure to your question. I'd recommend reviewing the previous question you asked and marking that as answered if that answer helped you. — zedfoxus
– zedfoxus, Commented Mar 19, 2020 at 6:00

zedfoxus · Accepted Answer · 2020-03-19 05:57:29Z

1

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

answered Mar 19, 2020 at 5:57

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ThataL · Accepted Answer · 2020-03-19 06:07:50Z

0

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

answered Mar 19, 2020 at 6:07

ThataL

1653 silver badges12 bronze badges

Collectives™ on Stack Overflow

Fetch records from multiple tables using join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related