Combining two MySQL Queries (One to select contents of another)

Question

How would I combine the following two quieries to produce a combination of the two?

SELECT * 
  FROM post_attributes 
  WHERE name IN ('title', 'body');

SELECT id, url, created_at, name, value 
  FROM posts, post_attributes 
 WHERE posts.id = post_attributes.post_id
 ORDER BY id;

The idea would be to extract the post number, it's url, the time it was created, the title and body of the blog post from the blogging engine Chyrp, but disregard the other data stored in the same database.

I am thinking that I am going about this in the wrong manner. Would there be a more appropriate function to use?

Answer 1

Are you looking for this:

SELECT id, url, created_at, name, value, post_attributes.*
  FROM posts, post_attributes 
 WHERE posts.id = post_attributes.post_id
   AND name IN ('title', 'body')
 ORDER BY id;

Answer 2

Not sure if this is the most efficient, but this should work (assuming that post_attributes has a column named 'value' that stores the attribute value).

SELECT id, url, created_at, name, value, p_title.value, p_body.value
FROM posts p
INNER JOIN post_attributes p_title on p.id = p_title.post_id 
INNER JOIN post_attributes p_body on p.id = p_body.post_id
ORDER BY p.id;

Answer 3

SELECT posts.id, posts.url, posts.created_at, posts.name, posts.value 
  FROM posts, post_attributes 
 WHERE posts.id = post_attributes.post_id
 AND post_attributes.name IN ('title', 'body')
 ORDER BY id;

Add a post_attributes.* to the end if you want all the fields from post attributes.

Answer 4

Awesome performance:

select p.id, pa.title, pa.body, pa.created_at, pa.name, pa.value from post_attributes pa, posts p where p.id = pa.post_id and (pa.name = 'title' or pa.name = 'body')