CODEWITHSUNDEEP
mysqlsqljoinleft-join
MHF
MHF

Reputation: 221

Nested join query for 'posts' and 'postmeta' wordpress tables

I have some data in the posts table and in the postmeta i have some meta_key with different values, for example:

meta_key = 'phone'
meta_value = 987654321

meta_key = 'score'
meta_value = 40

It is obvious that for a phone number there can be several points recorded.
I want to get total points of a phone number with its relation to the posts.
That query seems very complicated !!!

What I've tried is similar to this one: 

SELECT p.*, pm.* from wp_posts AS r    
LEFT JOIN wp_postsmeta AS pm    
ON pm.post_id = r.id    
WHERE (pm.meta_key = 'score')    
LEFT JOIN wp_postsmeta AS p     
ON p.post_id = r.id    
WHERE p.meta_key = 'phone'

How should I implement this?

Upvotes: 0

Views: 2600

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The correct syntax is:

SELECT p.*, pms.meta_value as score, pmp.meta_value as phone
FROM wp_posts p LEFT JOIN
     wp_postsmeta pms    
     ON pms.post_id = p.id AND pms.meta_key = 'score' LEFT JOIN   
     wp_postsmeta pmp     
     ON pmp.post_id = p.id AND pmp.meta_key = 'phone' ;

Notes:

  • Table aliases should make sense. r does not make sense for wp_posts, but p does.
  • p.* is a very odd thing to select. I assume you really want the posts table.
  • You should give the values returned by the query reasonable names, such as score and phone.
  • The where clauses are both misplaced and misused in your query. Those conditions should be in the on clauses.

EDIT:

Your revised question seems to suggest:

SELECT pmp.meta_value as phone, SUM(pms.meta_value) as score, 
FROM wp_postsmeta pms LEFT JOIN 
     wp_postsmeta pmp     
     ON pmp.post_id = pms.post_id AND pmp.meta_key = 'phone' and pms.meta_key = 'score'
GROUP BY phone;

Upvotes: 1

Related Questions