I've had to do this a couple times, and every time, I look around on Google for some good solutions, but don't find much. Basically, I have two tables of data, and I want to see if there are any rows in the first table that aren't in the second (or, conversely, I only want values that are in the first table AND the second).
To select rows in the first table that don't have any corresponding values in the second, try:
<br />
SELECT first.*<br />
FROM first_table first<br />
LEFT JOIN second_table second ON first.id = second.id<br />
WHERE second.id IS NULL<br />Conversely, if you just want to select rows in the first table that are also in the second (but discard rows that don't have corresponding values in the second), try:
<br />
SELECT first.*<br />
FROM first_table first<br />
LEFT JOIN second_table second ON first.id = second.id<br />
WHERE second.id IS NOT NULL<br />For my Drupal site, I needed to do something like:
<?php
<br /
?>FROM {custom_subscriptions} subs
LEFT JOIN {flag_content} flag ON flag.content_id = subs.content_id AND flag.uid = subs.uid
WHERE subs.content_id = :content_id
AND flag.content_id IS NOT NULL", array(
':content_id' => $content_id,
))->fetchAllAssoc('uid');
?>
Hope this helps!
Comments
Why not use NOT IN ?