I have a MySQL query that joins two tables
They join on voters.household_id
and household.id
.
Now what I need to do is to modify it where the voter table is joined to a third table called elimination, along voter.id
and elimination.voter_id
. However the catch is that I want to exclude any records in the voter table that have a corresponding record in the elimination table.
How do I craft a query to do this?
This is my current query:
SELECT `voter`.`ID`, `voter`.`Last_Name`, `voter`.`First_Name`,
`voter`.`Middle_Name`, `voter`.`Age`, `voter`.`Sex`,
`voter`.`Party`, `voter`.`Demo`, `voter`.`PV`,
`household`.`Address`, `household`.`City`, `household`.`Zip`
FROM (`voter`)
JOIN `household` ON `voter`.`House_ID`=`household`.`id`
WHERE `CT` = '5'
AND `Precnum` = 'CTY3'
AND `Last_Name` LIKE '%Cumbee%'
AND `First_Name` LIKE '%John%'
ORDER BY `Last_Name` ASC
LIMIT 30
This question is related to
mysql
join
not-exists
There are three possible ways to do that.
Option
SELECT lt.* FROM table_left lt
LEFT JOIN
table_right rt
ON rt.value = lt.value
WHERE rt.value IS NULL
Option
SELECT lt.* FROM table_left lt
WHERE lt.value NOT IN
(
SELECT value
FROM table_right rt
)
Option
SELECT lt.* FROM table_left lt
WHERE NOT EXISTS
(
SELECT NULL
FROM table_right rt
WHERE rt.value = lt.value
)
I'd use a 'where not exists' -- exactly as you suggest in your title:
SELECT `voter`.`ID`, `voter`.`Last_Name`, `voter`.`First_Name`,
`voter`.`Middle_Name`, `voter`.`Age`, `voter`.`Sex`,
`voter`.`Party`, `voter`.`Demo`, `voter`.`PV`,
`household`.`Address`, `household`.`City`, `household`.`Zip`
FROM (`voter`)
JOIN `household` ON `voter`.`House_ID`=`household`.`id`
WHERE `CT` = '5'
AND `Precnum` = 'CTY3'
AND `Last_Name` LIKE '%Cumbee%'
AND `First_Name` LIKE '%John%'
AND NOT EXISTS (
SELECT * FROM `elimination`
WHERE `elimination`.`voter_id` = `voter`.`ID`
)
ORDER BY `Last_Name` ASC
LIMIT 30
That may be marginally faster than doing a left join (of course, depending on your indexes, cardinality of your tables, etc), and is almost certainly much faster than using IN.
Source: Stackoverflow.com