mysql 选择前4个_mysql从4个表中选择

不要认为GROUP BY是必需的 . 虽然如果一个孩子有2个父记录，你可能想用它来将2个父母分组到一行 - 但不确定这是否是你的要求 . 因为如果一个孩子有2个父母，那么将为该孩子返回的父母是未定义的 .

假设所有孩子都有父母，所有父母都会有姓，姓和电子邮件： -

SELECT

k.*,

p.*,

u1.meta_value AS parent_lastname,

u2.meta_value AS parent_firstname,

u3.meta_value AS parent_email

FROM kids k

INNER JOIN relations r ON k.ID = r.kid_id

INNER JOIN parents p ON p.user_id = r.user_id

INNER JOIN usermeta u1 ON r.user_id = u1.user_id AND u1.meta_key = 'last_name'

INNER JOIN usermeta u2 ON r.user_id = u1.user_id AND u1.meta_key = 'first_name'

INNER JOIN usermeta u3 ON r.user_id = u1.user_id AND u1.meta_key = 'email'

如果该假设是错误的，则将INNER JOIN更改为LEFT OUTER JOIN

SELECT

k.*,

p.*,

u1.meta_value AS parent_lastname,

u2.meta_value AS parent_firstname,

u3.meta_value AS parent_email

FROM kids k

LEFT OUTER JOIN relations r ON k.ID = r.kid_id

LEFT OUTER JOIN parents p ON p.user_id = r.user_id

LEFT OUTER JOIN usermeta u1 ON r.user_id = u1.user_id AND u1.meta_key = 'last_name'

LEFT OUTER JOIN usermeta u2 ON r.user_id = u1.user_id AND u1.meta_key = 'first_name'

LEFT OUTER JOIN usermeta u3 ON r.user_id = u1.user_id AND u1.meta_key = 'email'