Using NOT Exists in SQL
 

I'm not sure if this is the best approach or not but I have a query
that returns about 28k records. In part there is a join that reads:

inner join EARoutAL on earoutings.id = earoutal.routid and
earoutings.routtype in (3,4,5)

This works fine. However it was discovered that there are many, about
6k records that have NO ROUTTYPE. So I was thinking of just adding the
NOT Exists to the end but I'm either very wrong in my thinking or I
have the syntax wrong. My luck, it will be both! So, I'm looking to
return all records that have a routtype of 3, 4 and 5 and those records
that do not have a routtype at all. Any suggestions?

Using NOT Exists in SQL
 

Since the filtering is happening on the join clause, this could get tricky.
First move filter to a where clause
WHERE earoutings.routtype in (3,4,5) or earoutings.routtype is null
If that does not work, you will have to use a subquery.



--
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 40
--------------------------------------------
Share your knowledge. Add your favorite questions and answers
Help add questions to this site! We want Your input.
--------------------------------------------
wrote in message
oups.com...
I'm not sure if this is the best approach or not but I have a query
that returns about 28k records. In part there is a join that reads:

inner join EARoutAL on earoutings.id = earoutal.routid and
earoutings.routtype in (3,4,5)

This works fine. However it was discovered that there are many, about
6k records that have NO ROUTTYPE. So I was thinking of just adding the
NOT Exists to the end but I'm either very wrong in my thinking or I
have the syntax wrong. My luck, it will be both! So, I'm looking to
return all records that have a routtype of 3, 4 and 5 and those records
that do not have a routtype at all. Any suggestions?

Using NOT Exists in SQL
 

How about:

inner join EARoutAL on
(earoutings.id = earoutal.routid)
where
earoutings.routtype in (3,4,5) or
earoutings.routtype is NULL

RBS

wrote in message
oups.com...
I'm not sure if this is the best approach or not but I have a query
that returns about 28k records. In part there is a join that reads:

inner join EARoutAL on earoutings.id = earoutal.routid and
earoutings.routtype in (3,4,5)

This works fine. However it was discovered that there are many, about
6k records that have NO ROUTTYPE. So I was thinking of just adding the
NOT Exists to the end but I'm either very wrong in my thinking or I
have the syntax wrong. My luck, it will be both! So, I'm looking to
return all records that have a routtype of 3, 4 and 5 and those records
that do not have a routtype at all. Any suggestions?