Not exists sql condition

[wgg]

Hello All

I would like to know if it is possible to get records printed in Crystal, which fit the following kind of 'not exists' condition.

****Example****
Select id1, name1 from table1 a where not exists (select id1, name1 from table2 b where b.id1 = a.id1)
***

NB I would like the records printed in table1 which don't have matching records in table2. If this is possible how can it be done?

Thank you.

wgg

 

[FoxG]

Hi,

Sounds like a left outer join from table a to b
Use record selection isnull({b.id})

Hth,
Geoff

 

[idle]

A left outer join from A to B will give you records that DO have matching records, as well as those that don't. {b.id} will never be null in this result set
A record selection of IsNull{a.id} would work, although it would be slower than using a where not exists... statement in a sp.

 

[idle]

Sorry, that should be A right outer join B where A.id is null. Need to drink some more coffee...

 

[FoxG]

Hi,

I still think this will work as a left outer.

If the details are missing from B than then you need a left outer from A to B to display details from A with no B key field !!

Geoff

 

[kenhamady]

This is trickier than it looks at first.

It has to be done with a left outer join to get both records that exist and don't exist. Then add a group and a subtotal that counts occurances of the records in the outer table for each ID. Then you use group selection to check for groups with a subtotal count = 0. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[wgg]

Thanks guys for your suggestions, I went off working on another project and only just got to read your replys. I give them a try and let you know the results.

Thanks again.

wgg