Wordpress / Custom MySQL queries

I have got a question to come back all posts from the specific category:

SELECT  ID, post_title, post_name, guid, post_date, post_content, guid FROM wp_posts as p
INNER JOIN wp_term_relationships AS tr ON
(p.ID = tr.object_id AND
tr.term_taxonomy_id IN (4) )
INNER JOIN wp_term_taxonomy AS tt ON
(tr.term_taxonomy_id = tt.term_taxonomy_id AND
taxonomy = 'category')
ORDER BY id DESC LIMIT 10

However I likewise need this question to come back the key phrases connected with every publish. I began to produce a "key phrases by publish ID" query, but recognized that might be a really pricey call when repeated.

Any idea regarding how to request the key phrases/terms for every publish that returns? Comma-separated works, I suppose?

SOLUTION:

No pretty method of doing it in MySQL -- joining all individuals tables could be nasty. So, the answer is three steps:

1) obtain the posts:

SELECT DISTINCT ID, post_title, post_name, guid, post_date, post_content, guid , GROUP_CONCAT(k.term_taxonomy_id) as keywords FROM wp_posts as p
INNER JOIN wp_term_relationships AS tr ON
(p.ID = tr.object_id AND
tr.term_taxonomy_id IN (4) )
INNER JOIN wp_term_relationships as k ON
(p.ID = k.object_id)
INNER JOIN wp_term_taxonomy AS tt ON
(tr.term_taxonomy_id = tt.term_taxonomy_id AND
taxonomy = 'category')
GROUP BY p.ID
ORDER BY id DESC LIMIT 10

The "group_concat" will return "key phrases" back as comma-separated ID's

2) obtain the key phrases from wordpress_terms

3) use PHP to find information about key phrases from that array (#2) when needed

You are able to choose all key phrases and posts ids with another query and mix both leads to greater level (PHP - I suppose you utilize it). You'll call database only two times, however you'll have to write more logic in PHP level.

second result would seem like this:

post_id | keyword 
-----------------
      2 | keyword1
      2 | keyword2 
      3 | keyword3 
      4 | keyword2 
      4 | keyword10