Ketan Lathiya Ketan Lathiya - 3 months ago 9
MySQL Question

mysql retrive data from 3 tables

I have 3 tables with user_id is common column in all 3 tables. I want only those rows from 3 tables which has

user_id = 4
.
For e.g.
I have 3 tables as below


  1. table1 (user_id, user_name)

  2. table2 (user_id, user_email)

  3. table3 (user_id, user_password)



Now, I want rows from all 3 tables which has
user_id = 4
.

I have tried below query but not getting success:

SELECT t1.user_name,t2.user_email,t3.user_password
FROM table1 as t1
LEFT JOIN table2 as t2 ON t1.user_id = t2.user_id
LEFT JOIN table3 as t3 ON t1.user_id = t3.user_id
WHERE t1.user_id = '4'


Please let me know, how to get data

Answer

What you probably need is UNION and not JOIN

select 'user_name' as attribite, user_name as entry 
from table1 
where user_id = '4'
union all
select 'user_email' as attribite, user_email as entry 
from table2 
where user_id = '4'
union all
select 'user_password' as attribite, user_password as entry 
from table3 
where user_id = '4'

http://sqlfiddle.com/#!9/35bf17/3

If you use JOIN like you did, you will get all combinations of username, email and password for one user.