Eric Cumbee Eric Cumbee - 2 years ago 107
SQL Question

MySQL Join Where Not Exists

I have a MySQL query that joins two tables

  • Voters

  • Households
    they join on voters.household_id and

    Now what i need to do is to modify it where the voter table is joined to a third table called elimination, along and elimination.voter_id, how ever 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

Answer Source

Try adding the following to your WHERE clause:

AND `voter`.`id` NOT IN (SELECT `voter_id` FROM (`elimination`))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download