mysql - 连接 2 个具有不同行数的表

Question

我想在一个 SQL 查询中连接 2 个表(contact 和 contact_meta)，因为我知道 contact_meta 有几行

联系方式

id    fname    lname      email
1     Nick     John       njohn@gmail.com
2     Laura    Pitt       lpitt@gmail.com

联系人元

id_contact      contact_meta_key      contact_meta_value
1               Newsletter            yes
1               Level                 weak
2               Newsletter            yes

我试过了

SELECT * FROM contact as c 
JOIN contact_meta as cm  ON c.id = cm.id_contact

但是如果 contact_meta 中有不止一行，我只得到最后一行。

Array ( 
    [0] => stdClass Object ( [id] => 1 [lname] => Nick [fname] => John [email] => njohn@gmail.com [contact_meta_key] => level [contact_meta_value] => weak ) 
    [1] => stdClass Object ( [id] => 2 [lname] => Laura [fname] => pitt [email] => lpitt@gmail.com [contact_meta_key] => newsletter [contact_meta_value] => yes 
);

此外，我想要 [newsletter] => yes 而不是 [contact_meta_key] => newsletter [contact_meta_value] => yes

我的愿望:

Array ( 
    [0] => stdClass Object ( [id] => 1 [lname] => Nick [fname] => John [email] => njohn@gmail.com [level] => weak [newsletter] => yes) 
    [1] => stdClass Object ( [id] => 2 [lname] => Laura [fname] => pitt [email] => lpitt@gmail.com [newsletter] => yes
);

Answer 1

您需要旋转元表。

SELECT c.*,
    MAX(IF(contact_meta_key = 'Newsletter', contact_meta_value, NULL)) AS newsletter,
    MAX(IF(contact_meta_key = 'Level', contact_meta_value, NULL)) AS level
FROM contact AS c
LEFT JOIN contact_meta AS cm ON c.id = cm.id_contact
GROUP BY c.id

DEMO