PHP SQL连接表行作为键[关闭]

I was wondering if someone could help me. I have the following two tables:

---Posts----

id  | title | content |  
544 | Alan  | Blah

---Postmeta----

metaid | post_id | meta_key | meta_value  
1      |  544    | age      | 45  
2      |  544    | email    | test@hotmail.co.uk

I want to join the two tables by post_id and then display the meta_keys. Something like this

SELECT posts.post_id, 
       postmeta.age, 
       postmeta.email, 
FROM 
       posts  
JOIN posts 
ON posts.post_id = postmeta.post_id 

Any help would be much appreciated. Cheers

You need to PIVOT the rows inside the table postmeta into columns, using CASE with MAX as an aggregate function to eliminate NULLs like so:

SELECT p.id, pm.age, pm.email
FROM posts p
JOIN
(
   SELECT
     post_id,
     MAX(CASE WHEN meta_key = 'age'   THEN  meta_value END) AS age,
     MAX(CASE WHEN meta_key = 'email' THEN meta_value END) AS email
   FROM postsmeta
   GROUP BY post_id
) pm ON p.id = pm.post_id

Here is a Demo in sql fiddle

Note that: You are currently storing all values for age and email in one column of the same data type that would be varchar for example then you have to cast the age values to INT in the query in order to get it like INT if you want to perform some calculations later on, and I left this part for you.

it should be like this

SELECT posts.post_id,
postmeta.age,
postmeta.email,
FROM
posts
JOIN postsmeta
ON posts.post_id = postmeta.post_id

SELECT
  posts.id,
  postmeta.key,
  postmeta.meta_value
FROM
  posts
JOIN
  postmeta ON posts.id = postmeta.post_id
WHERE
  postmeta.key IN ('age', 'email')

SELECT posts.id, 
       postmeta.age, 
       postmeta.email, 
       m.meta_key, 
       m.meta_value
FROM 
       posts  p
JOIN Postmeta m
ON p.id = m.post_id 

Joining twice to the postmeta table:

SELECT p.id, 
       pm1.meta_value AS age, 
       pm2.meta_value AS email
FROM 
       posts AS p
  LEFT JOIN 
       postmeta AS pm1
    ON pm1.post_id = p.id
   AND pm1.meta_key = 'age'

  LEFT JOIN 
       postmeta AS pm2
    ON pm2.post_id = p.id 
   AND pm2.meta_key = 'email' ;

Instead of saving meta_key in a new row, try to make your table structure like this

metaid | post_id | age | email  
1      |  544    | 45  | test1@hotmail.co.uk  
2      |  545    | 51  | test2@hotmail.co.uk  
3      |  546    | 20  | test3@hotmail.co.uk  
4      |  547    | 26  | test4@hotmail.co.uk  

you can add more fields to table structure.

and after that you can make a simple join query, that will save your time and resources to execute the query. like this

SELECT posts.post_id, postmeta.age, postmeta.email FROM posts JOIN postmeta ON posts.post_id = postmeta.post_id;