Hi

Lets say I have three tables:

tblMain:
Index: AutoInc
Name: String

tblSubject
Index: AutoInc
Subject: String

tblLink (which obviously links the items in the two tables above in a many to many relationship)
NameIndex: Integer
SubjectIndex: Integer


What SQL could i use to return a list of all the Names in tblMain that do not have an entry in tblLink?


Thanks SQL Gurus!

Dave <davey.crackpot@gmail.com> wrote on Wed, 8 Feb 2006 21:15:13 -0500

>Hi
>
>Lets say I have three tables:
>
>tblMain:
>Index: AutoInc
>Name: String
>
>tblSubject
>Index: AutoInc
>Subject: String
>
>tblLink (which obviously links the items in the two tables above in a many to many relationship)
>NameIndex: Integer
>SubjectIndex: Integer
>
>
>What SQL could i use to return a list of all the Names in tblMain that do not have an entry in tblLink?
>
>
>Thanks SQL Gurus!
>
>
>
Dave


SELECT M.Name FROM tblMain M
LEFT OUTER JOIN tblLink L ON L.NameIndex = M.Index
WHERE L.NameIndex IS NULL

Cheers

Jeff (no guru!)



--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Thanks Jeff, works great.