[NOT IN] OR [NOT EXISTS] - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[NOT IN] OR [NOT EXISTS]

Author

Topic

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-05 : 08:16:40

Hey all

I would like records that are in (A + B) and All from A that are not in B.

So far I have:

SELECT All_Events.*, PC_AllData.FC, PC_AllData.FN
FROM All_Events, PC_AllData
WHERE (((All_Events.EDate)>=#4/1/2008# And (All_Events.EDate)<=#4/30/2008#) AND ((All_Events.ID)=[PC_AllData].[ID]))

Along with the above which will give me all the matches, I want those from All_Events that are not in PC_AllData

How would I get these?

Many thanks,

Rupa

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-05 : 08:29:37

Try this:
SELECT All_Events.*,
PC_AllData.FC,
PC_AllData.FN
FROM All_Events
left join PC_AllData on (All_Events.ID = PC_AllData.ID)
WHERE 1=1
AND All_Events.EDate>=#4/1/2008#
And All_Events.EDate<=#4/30/2008#
AND PC_AllData.ID IS NULL

Webfred

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-05 : 08:31:53

I now have:

SELECT All_Events.*, PC_AllData.FC, PC_AllData.FN
FROM All_Events, PC_AllData
WHERE (((All_Events.EDate)>=#4/1/2008# And (All_Events.EDate)<=#4/30/2008#) AND ((All_Events.ID)=[PC_AllData].[ID]) AND
WHERE NOT EXISTS (SELECT *
FROM All_Events, PC_AllData
WHERE All_Events.ID=PC_AllData.ID))

I now have the following error message:

Syntax error in expression...

Any pointers will be highly appreciated.

Many thanks,

Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-05 : 08:38:31

Thank you for your reply Webfred..

Before I added the criteria where a.ID=b.ID, I had 277,814 records and I now have 19,339 when I used your suggested query.

I should have 277,814 records.

Any ideas??

Thanks,

Rupa

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-05 : 08:41:00

Try

SELECT All_Events.*,
PC_AllData.FC,
PC_AllData.FN
FROM All_Events
left join PC_AllData on (All_Events.ID = PC_AllData.ID)
AND All_Events.EDate>=#4/1/2008#
And All_Events.EDate<=#4/30/2008#

Are you using Access?

Madhivanan

Failing to plan is Planning to fail

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-05 : 08:50:49

Yes Madhivanan, I am..Sorry, I know this is the wrong forum but I need help.

I tried your suggested query and I got an error: Join expression not supported and it highlights: All_Events.EDate>=#4/1/2008#

Any ideas?

Your help is much appreciated!

Thanks,
Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-05 : 08:53:42

I have just changed:

SELECT All_Events.*,
PC_AllData.FC,
PC_AllData.FN
FROM All_Events
left join PC_AllData on (All_Events.ID = PC_AllData.ID)
AND All_Events.EDate>=#4/1/2008#
And All_Events.EDate<=#4/30/2008#

To

SELECT All_Events.*,
PC_AllData.FC,
PC_AllData.FN
FROM All_Events
left join PC_AllData on (All_Events.ID = PC_AllData.ID)
WHERE All_Events.EDate>=#4/1/2008#
And All_Events.EDate<=#4/30/2008#

It now gives me 282,127 records. Close but more than 277,814.

There might be a valid reason for this.

I'll look into this.

Many thanks,

Rupa

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-05 : 09:08:24

quote:
Originally posted by webfred

Try this:
SELECT All_Events.*,
PC_AllData.FC,
PC_AllData.FN
FROM All_Events
left join PC_AllData on (All_Events.ID = PC_AllData.ID)
WHERE 1=1
AND All_Events.EDate>=#4/1/2008#
And All_Events.EDate<=#4/30/2008#
AND PC_AllData.ID IS NULL

Webfred

I want those from All_Events that are not in PC_AllData
How would I get these?

Thats it!
surely there are not so many rows!

Am I right?

Webfred

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-05 : 10:08:32

Sorry Webfred..I don't understand your question..

If it helps, All_Events table contains 1,600,448 records..

Rupa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-05 : 13:22:25

quote:
Originally posted by Rupa

I have just changed:

SELECT All_Events.*,
PC_AllData.FC,
PC_AllData.FN
FROM All_Events
left join PC_AllData on (All_Events.ID = PC_AllData.ID)
AND All_Events.EDate>=#4/1/2008#
And All_Events.EDate<=#4/30/2008#

To

SELECT All_Events.*,
PC_AllData.FC,
PC_AllData.FN
FROM All_Events
left join PC_AllData on (All_Events.ID = PC_AllData.ID)
WHERE All_Events.EDate>=#4/1/2008#
And All_Events.EDate<=#4/30/2008#

It now gives me 282,127 records. Close but more than 277,814.

There might be a valid reason for this.

I'll look into this.

Many thanks,

Rupa

This can happen if there is a one to many relationship between A & B. i.e more than one record of B (PC_AllData )have same value for PC_AllData.ID

Rupa
Posting Yak Master

123 Posts

Posted - 2008-08-06 : 09:18:26

Thank you all for your help but it's still not correct..

I want something like:

SELECT *
FROM A, B
WHERE A.id = B.id
UNION
SELECT *
FROM A
WHERE A.id <> B.id

I know this is done using a left join but it's not giving me the correct numbers.

Any guidance will be highly appreciated.

Many thanks,

Rupa

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-06 : 09:20:01

maybe this will clear the difference between the two:
http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources