Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
Joining Tables |
Tue, Jan 31 2006 4:26 AM | Permanent Link |
"Jack Marsh" | 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 |
Tue, Jan 31 2006 5:12 AM | Permanent Link |
"Ralf Mimoun" | 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 |
Tue, Jan 31 2006 5:14 AM | Permanent Link |
"Ralf Mimoun" | 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 |
Tue, Jan 31 2006 11:00 AM | Permanent Link |
"Jack Marsh" | 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 > |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |