Menu
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.
376
The self join is often used to query hierarchical data or to compare a row with other rows within the same table. To perform a self join, you must use table aliases to not repeat the same table name twice in a single query.
As you may know, it is used to join and combine data from two or more tables into one common data set. In this article, I'm going to discuss special types of joins? in which you combine the same table twice—including joining a table to itself, also known as the self join.
The self-join is a special kind of joins that allow you to join a table to itself using either LEFT JOIN or INNER JOIN clause. You use self-join to create a result set that joins the rows with the other rows within the same table.
To form a self-join, you specify the same table twice with different table aliases and provide the join predicate after the ON keyword. In this syntax, the table_name is joined to itself using the INNER JOIN clause.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
