如何显示来自不同表的数据？

Question

Table 1
| ID | Post_Name  | Post_Status | Parent_ID | Post_Content | Post_Type |
+----+------------+-------------+-----------+--------------+-----------+
| 1  | John Sample| publish     | 0         | Content Text | person    |
| 2  |            | inherit     | 1         | image1.jpg   | attachment|
| 3  |            | inherit     | 1         | image2.jpg   | attachment|
| 4  |            | inherit     | 1         | image3.jpg   | attachment|
| 5  | Mark Sample| publish     | 0         | Mark  Text   | person    |
| 6  |            | inherit     | 5         | image1.jpg   | attachment|
| 7  |            | inherit     | 5         | image2.jpg   | attachment|
| 8  |            | inherit     | 5         | image3.jpg   | attachment|

Table 2
| meta_id | post_id  |    meta_key    |      meta_value       |
+---------+----------+----------------+-----------------------+
|   1     |    1     |  Address       | Sample Street,Country |
|   2     |    1     |  Phone         | 10000000000000        |
|   3     |    1     |  Sex           | Male                  |
|   4     |    1     |  Primary pic   | 2                     |
|   5     |    2     |  Thumbnail     | John_thumbnail.jpg    |
|   6     |    2     |  meta_data     | data_data_data        |
|   7     |    5     |  Address       | Mark Street,Country   |
|   8     |    5     |  Phone         | 20000000000000        |
|   9     |    5     |  Sex           | Male                  |
|   10    |    5     |  Primary pic   | 6                     |
|   11    |    6     |  Thumbnail     | Mark_thumbnail.jpg    |
|   12    |    6     |  meta_data     | data_data_data        |

I would like to display like this

Name: John Sample
Comment: Content Text
Address: Sample Street, Florida
Phone: [phone]
Sex: Male
Thumbnail: John_thumbail.jpg
Photo: images1.jpg, images2.jpg, images3.jpg

Name: Mark Sample
Comment: Mark Text
Address: Mark Street, Florida
Phone: [phone]
Sex: Male
Thumbnail: Mark_thumbail.jpg
Photo: images1.jpg, images2.jpg, images3.jpg

Answer 1

Try this:

SELECT
  t.Post_Name AS Name,
  MAX(CASE WHEN t2.meta_key = 'Address' THEN t2.meta_value END) AS 'Address',
  MAX(CASE WHEN t2.meta_key = 'Phone' THEN t2.meta_value END) AS 'Phone',
  MAX(CASE WHEN t2.meta_key = 'Sex' THEN t2.meta_value END) AS 'Sex',
  GROUP_CONCAT(DISTINCT p.Post_Content  SEPARATOR ',') AS Photo
FROM Table1 p
INNER JOIN table1 t ON p.parent_ID = t.ID
INNER JOIN Table2 t2 ON t.Id = t2.post_id
GROUP BY p.Post_Name;

SQL Fiddle Demo

This will give you:

|        NAME |               ADDRESS |          PHONE |  SEX |                            PHOTO |
--------------------------------------------------------------------------------------------------
| John Sample | Sample Street,Country | 10000000000000 | Male | image3.jpg,image2.jpg,image1.jpg |

---

Then using PHP, you can display them the way you want.

---

Update

If you want to display the value of user comment as comment, you can do this:

SELECT
  t.Post_Name AS Name,
  MAX(CASE WHEN t2.meta_key = 'Address' THEN t2.meta_value END) AS 'Address',
  MAX(CASE WHEN t2.meta_key = 'Phone' THEN t2.meta_value END) AS 'Phone',
  MAX(CASE WHEN t2.meta_key = 'Sex' THEN t2.meta_value END) AS 'Sex',
  GROUP_CONCAT(DISTINCT p.Post_Content  SEPARATOR ',') AS Photo,
  GROUP_CONCAT(DISTINCT t.post_Content) comment
FROM Table1 p
INNER JOIN table1 t ON p.parent_ID = t.ID
INNER JOIN Table2 t2 ON t.Id = t2.post_id
GROUP BY p.Post_Name;

Updated SQL fiddle Demo

---

Update2:

Based on the updated sample data:

SELECT
  p.Post_Name AS Name,
  MAX(CASE WHEN t.meta_key = 'Address' THEN t.meta_value END) AS 'Address',
  MAX(CASE WHEN t.meta_key = 'Phone' THEN t.meta_value END) AS 'Phone',
  MAX(CASE WHEN t.meta_key = 'Sex' THEN t.meta_value END) AS 'Sex',
  GROUP_CONCAT(DISTINCT c.Post_Content  SEPARATOR ',') AS Photo,
  GROUP_CONCAT(DISTINCT p.post_Content) comment
FROM
(
  SELECT *
  FROM Table1 
  WHERE parent_id = 0
) AS p
INNER JOIN table1 AS c ON p.ID      = c.parent_ID
INNER JOIN table2 AS t ON t.post_id = p.id
GROUP BY p.post_Name;

SQL Fiddle Demo

This will give you:

|        NAME |               ADDRESS |          PHONE |  SEX |                            PHOTO |      COMMENT |
-----------------------------------------------------------------------------------------------------------------
| John Sample | Sample Street,Country | 10000000000000 | Male | image2.jpg,image1.jpg,image3.jpg | Content Text |
| Mark Sample |   Mark Street,Country | 20000000000000 | Male | image3.jpg,image1.jpg,image2.jpg |   Mark  Text |

---

Update 2

For the updated data, try this:

SELECT 
  t1.*,
  t2.meta_value AS 'Thumbnail'
FROM
(
  SELECT
    p.Post_Name AS Name,
    MAX(CASE WHEN t.meta_key = 'Address' THEN t.meta_value END) AS 'Address',
    MAX(CASE WHEN t.meta_key = 'Phone' THEN t.meta_value END) AS 'Phone',
    MAX(CASE WHEN t.meta_key = 'Sex' THEN t.meta_value END) AS 'Sex',
    MAX(CASE WHEN t.meta_key = 'Primary pic' THEN t.meta_value END) AS 'Primary_pic',
    GROUP_CONCAT(DISTINCT c.Post_Content  SEPARATOR ',') AS Photo,
    GROUP_CONCAT(DISTINCT p.post_Content) comment
  FROM
  (
    SELECT *
    FROM Table1 
    WHERE parent_id = 0
  ) AS p
  INNER JOIN table1 AS c ON p.ID      = c.parent_ID
  INNER JOIN table2 AS t ON t.post_id = p.id
  GROUP BY p.post_Name
) AS t1
INNER JOIN table2 t2  ON t1.Primary_pic = t2.post_id
                     AND t2.meta_key = 'Thumbnail';

Updated SQL Fiddle Demo

This will give you:

|        NAME |               ADDRESS |          PHONE |  SEX | PRIMARY_PIC |                            PHOTO |      COMMENT |          THUMBNAIL |
----------------------------------------------------------------------------------------------------------------------------------------------------
| John Sample | Sample Street,Country | 10000000000000 | Male |           2 | image3.jpg,image1.jpg,image2.jpg | Content Text | John_thumbnail.jpg |
| Mark Sample |   Mark Street,Country | 20000000000000 | Male |           6 | image1.jpg,image2.jpg,image3.jpg |   Mark  Text | Mark_thumbnail.jpg |

---

---

Update 3

For this new meta_data column you will need one more join to the second table as we did for the primary pic like this:

SELECT 
  t1.Name,
  t1.Address,
  t1.Phone,
  t1.Sex,
  t1.Photo,
  t1.Comment,
  t2.meta_value AS 'Thumbnail',
  t3.meta_value AS 'meta_data'
FROM
(
  SELECT
    p.Post_Name AS Name,
    MAX(CASE WHEN t.meta_key = 'Address' THEN t.meta_value END) AS 'Address',
    MAX(CASE WHEN t.meta_key = 'Phone' THEN t.meta_value END) AS 'Phone',
    MAX(CASE WHEN t.meta_key = 'Sex' THEN t.meta_value END) AS 'Sex',
    MAX(CASE WHEN t.meta_key = 'Primary pic' THEN t.meta_value END) AS 'Primary_pic',
    GROUP_CONCAT(DISTINCT c.Post_Content  SEPARATOR ',') AS Photo,
    GROUP_CONCAT(DISTINCT p.post_Content) comment
  FROM
  (
    SELECT *
    FROM Table1 
    WHERE parent_id = 0
  ) AS p
  INNER JOIN table1 AS c ON p.ID      = c.parent_ID
  INNER JOIN table2 AS t ON t.post_id = p.id
  GROUP BY p.post_Name
) AS t1
INNER JOIN table2 t2  ON t1.Primary_pic = t2.post_id
                     AND t2.meta_key = 'Thumbnail'
INNER JOIN table2 t3  ON t1.Primary_pic = t3.post_id
                     AND t3.meta_key = 'meta_data';

Like this

This will give you:

|        NAME |               ADDRESS |          PHONE |  SEX |                            PHOTO |      COMMENT |          THUMBNAIL |      META_DATA |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| John Sample | Sample Street,Country | 10000000000000 | Male | image1.jpg,image2.jpg,image3.jpg | Content Text | John_thumbnail.jpg | data_data_data |
| Mark Sample |   Mark Street,Country | 20000000000000 | Male | image2.jpg,image3.jpg,image1.jpg |   Mark  Text | Mark_thumbnail.jpg | data_data_data |