MySQL -如何使用LEFT JOIN实现我想要的输出？

Question

以下查询：

   SELECT friendship.dom, friendship.sub,
          user.id, user.fname, user.lname
     FROM friendship
LEFT JOIN user ON friendship.dom = user.id

结果如下：

dom  sub  id  fname  lname 
---------------------------
2    1    2   Seun   Soti
1    2    1   Donal  Lynch

但是如何使输出看起来像这样：

dom  sub  id_dom  fname_dom  lname_dom  id_sub  fname_sub  lname_sub
----------------------------------------------------------------------
2    1    2       Seun       Soti       1       Donal      Lynch
1    2    1       Donal      Lynch      2       Seun       Soti

我只需要在输出中包含两个用户(dom和sub)。

Answer 1

这样如何：

SELECT 
  f.dom, f.sub,
  d.id dom_id, d.fname dom_fname, d.lname dom_lname,
  s.id sub_id, s.fname sub_fname, s.lname sub_lname
FROM friendship f
LEFT JOIN user d ON f.dom = d.id
LEFT JOIN user s ON f.sub = s.id

Answer 2

试试这个：

SELECT friendship.dom, friendship.sub, user.id AS id_dom, ...
FROM friendship
LEFT JOIN user ON friendship.dom = user.id

这对你来说应该是可行的。HTH

Answer 3

 SELECT friendship.dom, friendship.sub,
      user.id, user.fname, user.lname,
      u1.id, u1.fname, u1.lname
 FROM friendship 
 FROM friendship
 LEFT JOIN user ON friendship.dom = user.id 
 LEFT JOIN user as u1 ON friendship.sub = u1.id