php - mysql联合查询

Question

包含成员信息的表的结构如下:

   id   |    fname    |    pic    |   status
--------------------------------------------------
   1    |     john    |   a.jpg   |     1
   2    |     mike    |   b.jpg   |     1
   3    |     any     |   c.jpg   |     1
   4    |     jacky   |   d.jpg   |     1

friend 列表表如下所示:

  myid  |   date     |    user   
-------------------------------
    1   | 01-01-2011 |     4
    2   | 04-01-2011 |     3

我想做一个查询，结果将打印来自“friendlist”表的用户，该表包含来自“members”表的用户的照片和名称，myid(添加的人)和用户(添加的人) .

本例中的表格将如下所示:

   myid   |   myidname  |    myidpic   |  user  |  username  |  userpic  |  status   
 -----------------------------------------------------------------------------------
     1    |     john    |    a.jpg     |   4    |    jacky   |   d.jpg   |     1
     2    |     mike    |    b.jpg     |   3    |    any     |   c.jpg   |     1

Answer 1

应该这样做:

SELECT
    m1.id AS myid,
    m1.fname AS myidname,
    m1.pic AS myidpic,
    m2.id AS user,
    m2.fname AS username,
    m2.pic AS userpic,
    m1.status
FROM
    members m1
        INNER JOIN friends f ON m1.id = f.myid
        INNER JOIN members m2 ON f.user = m2.id