SQL Not Exist

[Eric]

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);

[Peter Koen]

"=?Utf-8?B?RXJpYw==?=" <an*******@discussions.microsoft.com> wrote in
news:66**********************************@microsof t.com:

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);

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)

--
best regards

Peter Koen
-----------------------------------
MCAD, CAI/R, CAI/S, CASE/RS, CAT/RS
http://www.kema.at

[Chris Dawe]

On Sat, 25 Oct 2003 14:36:00 -0700, Peter Koen
<koen-newsreply&snusnu.at> wrote:

"=?Utf-8?B?RXJpYw==?=" <an*******@discussions.microsoft.com> wrote in
news:66**********************************@microso ft.com:

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);

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)

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
--