CODEWITHSUNDEEP
phpmysqlsql
Rahul Dev
Rahul Dev

Reputation: 175

Joining two Mysql tables to fetch names for multiple columns

I am trying to fetch parent_name and sponsor_name from table "member" according to the member_id, parent_id and sponsor_id in member_tree table.

I tried the below query but i don't know why it didn't work.

SELECT mt.member_id, mt.parent_id, mt.sponsor_id, mt.member_name, m.member_name as parent_name , m1.member_name as sponsor_name 
FROM `member_tree` mt 
LEFT JOIN `member` m ON mt.parent_id = m.member_id 
LEFT JOIN `member` m1 ON mt.sponsor_id = m1.member_id 
WHERE mt.`member_id` IN ( 1000015,1000016,1000017,1000018,1000019,1000020,1000021,1000022,1000023,1000024,1000025,1000026,1000027,1000028,1000029,1000030 )

Mysql Table

Thank you.

Upvotes: 0

Views: 88

Answers (3)

Deepika Janiyani
Deepika Janiyani

Reputation: 1477

This query works fine, you can check it in the fiddle http://sqlfiddle.com/#!2/499e95/1

Just make sure that

Do you have member_name field in both the table, as you have written mt.member_name, and mt refers to member_tree table

If you don't have member name in the member_tree table use this query from the fiddle http://sqlfiddle.com/#!2/5d2bd/1

SELECT mt.member_id, mt.parent_id, mt.sponsor_id, m.member_name, 
m1.member_name as parent_name , m2.member_name as sponsor_name 
FROM `member_tree` mt 
LEFT JOIN 
`member` m ON mt.member_id = m.member_id 
LEFT JOIN 
`member` m1 ON mt.parent_id = m1.member_id 
LEFT JOIN 
`member` m2 ON mt.sponsor_id = m2.member_id 
WHERE mt.`member_id` 
IN (1000015,1000016,1000017,1000018,1000019,1000020,1000021,1000022,1000023,1000024,1000025,100    0026,1000027,1000028,1000029,1000030 )

Upvotes: 1

Chinthana
Chinthana

Reputation: 1567

Can you make sure that foreign keys exist for parent_id and sponsor_id. If not can you check member_ids 1000007 to 1000014 are existing in the member table.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

Looks OK to me - although I'd probably write it like this.

Upvotes: 0

Related Questions