CODEWITHSUNDEEP
mysqlsql
execv
execv

Reputation: 859

MySQL Join Same Table

I have the table 'meta_data' with the following fields:

I'd like to loop through and display a list of EACH post (post_id) that has an entry for meta_key='abc' but not one for meta_key='def'

Basically, every post that has a meta_key='abc' entry should have a meta_key='def' entry. I want to generate the list so I can add the missing meta_key='def' entries.

Upvotes: 19

Views: 43498

Answers (2)

Gratzy
Gratzy

Reputation: 9389

To achieve this you should use the LEFT OUTER JOIN operation joining the same table.

SELECT a.*
FROM meta_data a
LEFT OUTER JOIN meta_data b ON a.post_id = b.post_id AND b.meta_value = 'def'
WHERE 
a.meta_value = 'abc'
AND b.post_id IS null

Upvotes: 25

Bohemian
Bohemian

Reputation: 425448

Make an outer (left) join to itself, filtering on those records that don't match by looking for rows with a null id in the joined table:

select t1.* 
from meta_data t1
left join meta_data t2 on t2.post_id = t1.post_id and t2.meta_key='def' 
where t1.meta_key='abc'
and t2.id is null

Upvotes: 11

Related Questions