SQL Not Exist

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table called Elections where All is Yes/No

Title ... ... All

I also have a table called HasVoted

ID Title

And a table called Students

Name ID ... ...

I need to write a query that finds all the Titles in Election where All = yes and checks to make sure that the Title doesn't exist in HasVoted for a given ID ssn.

this didn't work but I thought it was close:

SELECT Title t FROM Elections WHERE Elections.All = 'Yes' AND NOT EXISTS (SELECT ID FROM HasVoted WHERE ID = '" + s + "' AND Title = t);
 
I have a table called Elections where All is Yes/No

Title ... ... All

I also have a table called HasVoted

ID Title

And a table called Students

Name ID ... ...

I need to write a query that finds all the Titles in Election where
All = yes and checks to make sure that the Title doesn't exist in
HasVoted for a given ID ssn.

this didn't work but I thought it was close:

SELECT Title t FROM Elections WHERE Elections.All = 'Yes' AND NOT
EXISTS (SELECT ID FROM HasVoted WHERE ID = '" + s + "' AND Title = t);
Click to expand...

SELECT Title FROM Election E WHERE Elections.ALL = 'Yes' AND NOT EXISTS
(SELECT ID FROM HasVoted V INNER JOIN Students S on V.ID = S.ID WHERE
V.Title = E.Title)
 
SELECT Title FROM Election E WHERE Elections.ALL = 'Yes' AND NOT EXISTS
(SELECT ID FROM HasVoted V INNER JOIN Students S on V.ID = S.ID WHERE
V.Title = E.Title)
Click to expand...

Why join to the Students table at all, it's unnecessary.

SELECT Title FROM Election E WHERE Elections.ALL = 'Yes' AND NOT
EXISTS (SELECT * FROM HasVoted V WHERE V.Title = E.Title)

or

SELECT Title FROM Election E WHERE Elections.ALL = 'Yes' AND Title NOT
IN (SELECT DISTINCT Title FROM HasVoted)

Chris
--
 
Back
Top