Posted by David on 04/26/06 17:50

Hi,

Is there a NOT operator in sql? I actually want NOT EXISTS as i believe
these two statements would get a complete data set. One gets the
records which also have a recording match, and then i want to get all
without. After this i was going to union the two sets together.

Thanks for the help

David

Erland Sommarskog wrote:
> David (david.goodyear@gmail.com) writes:
> > Hi, I was hoping someone could confirm the following about the
> > following number 2 SQL query:
> >
> > 1. This extracts the data required given that there is a recording to
> > match the records
> >
> > 2. This should, but im not convinced extract all the remaining records
> > that do not have a matched recording, but fulfil the rest of the
> > criteria. If not please help...
>
> These conditions seems to contradict each other.
>
> > 3. As the two querys produce the same layout of tables if I did a UNION
> > ALL, this should work shouldnt it?
>
> But why? Why not an outer join instead?
>
> > count
> > (
> > SELECT rec.*
> > WHERE rec.whole_phone_number = l.whole_phone_number AND
> > rec.last_name = l.last_name AND
> > rec.agent = ai.agent_login AND
> > rec.campaign = l.campaign
> > ) < 1
>
> An EXISTS clause is what you want. This is odd and less effiecient.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]