CODEWITHSUNDEEP
mysql
Md. Himel Ali
Md. Himel Ali

Reputation: 311

How to fetch data all (Just Like name, Father, Mother, Own name) data in single query by multple table in mysql?

I have two tables. Just like

----UserTable---- 

id  user    email       
1   admin   [email protected]
2   editor  [email protected]

----NameTable---- 

name    userid  fullname    mobile
own         1   Rahim       012314
father      1   Karim       120120
mother      1   Florin      212021

How to fetch data all (Just Like name, Father, Mother, Own name) data in single query in mysql?

Upvotes: 1

Views: 84

Answers (1)

Bohemian
Bohemian

Reputation: 425033

Join to the name table once for each name:

select id, u.user, email, o.fullname own, f.fullname father, m.fullname mother
from users u
left join nametable o on o.userid = u.id and o.name = 'own'
left join nametable f on f.userid = u.id and f.name = 'father'
left join nametable m on m.userid = u.id and m.name = 'mother'

Note how all conditions on the name table must be in the join condition to allow the join to remain an outer join.

Upvotes: 0

Related Questions