Hi,

I have two tables ("OnlyContacts" and "NotContacts"), both containing only a
single column: "ContactCode".  I also have a "Contacts" table that contains
contact information, including a unique "ContactCode".

I want to list the details of all Contacts that match "OnlyContacts" UNLESS
they also match "NotContacts".

The following statement lists all contacts that match "OnlyContacts", but
how do I extend this to exclude those matching "NotContacts"?

I am trying to avoid using INTERSECT and EXCEPT if possible.

SELECT ContactCode, Surname, Title INTO "\Memory\Results"
FROM Contacts c, "\Memory\OnlyContacts" o
WHERE c.ContactCode = o.ContactCode;

Thanks,
Jack

Jack Marsh wrote:
> Hi,
>
> I have two tables ("OnlyContacts" and "NotContacts"), both containing
> only a single column: "ContactCode".  I also have a "Contacts" table
> that contains contact information, including a unique "ContactCode".
>
> I want to list the details of all Contacts that match "OnlyContacts"
> UNLESS they also match "NotContacts".

Assuming that Contacts.ContactCode is never NULL, something like

SELECT *, OnlyContacts.ContactCode AS OnlyContactCode,
NotContacts.ContactCode AS NotContactCode
FROM Contacts
LEFT OUTER JOIN OnlyContacts ON Contacts.ContactCode =
OnlyContacts.ContactCode
LEFT OUTER JOIN NotContacts ON Contacts.ContactCode =
NotContacts.ContactCode
WHERE OnlyContactCode IS NOT NULL AND NotContactCode IS NULL

should work.

Ralf

A little bit cleaner...

SELECT *, OnlyContacts.ContactCode AS OnlyContactCode,
NotContacts.ContactCode AS NotContactCode
FROM Contacts
INNER JOIN OnlyContacts ON Contacts.ContactCode = OnlyContacts.ContactCode
LEFT OUTER JOIN NotContacts ON Contacts.ContactCode =
NotContacts.ContactCode
WHERE NotContactCode IS NULL

Ralf

Thanks a lot Ralf, that was exactly what I was trying to achieve, and very
simple!

Cheers,
Jack


"Ralf Mimoun" <nospam@rad-on.de> wrote in message
news:B1472CA2-E994-41BE-8E03-D969E34DF9BA@news.elevatesoft.com...
>A little bit cleaner...
>
> SELECT *, OnlyContacts.ContactCode AS OnlyContactCode,
> NotContacts.ContactCode AS NotContactCode
> FROM Contacts
> INNER JOIN OnlyContacts ON Contacts.ContactCode = OnlyContacts.ContactCode
> LEFT OUTER JOIN NotContacts ON Contacts.ContactCode =
> NotContacts.ContactCode
> WHERE NotContactCode IS NULL
>
> Ralf
>