How to Join in CR to simulate Not In from SQL

[JenL]

Have the following SQL query:

SELECT REFERENCE#
FROM TABLE_1
WHERE REFERENCE# NOT IN (SELECT REFERENCE# FROM TABLE_2)

So basically, only want the reference# from the first table where there is no occurence of that reference# in the second table.

Does anyone know how to simulate this in Crystal Reports?

Thanks!

JenL

 

[AdHoc]

Why not just use "Add Command" and type your SQL statement in there and base your report on the SQL command instead of directly using the tables?

But if you don't want to use an SQL command here is another way: Add Table_1 and Table_2 to a new report and link them on REFERENCE#. Then change the Link Options to make it a Left Outer Join from Table_1 to Table_2.

Next make your record selection formula to be:

IsNull({Table_2.REFERENCE#})

 

[Turkbear]

Hi,
The Link Options also allow for a != that should do that for you and may do it on the database side, although I have never used it to test.

 

[synapsevampire]

Adhoc: That method is for CR 9 and above only.

Options would be to create a View or SP, or manually edit the Database->Show SQL, or paste in the SQL using an ADO connection in CR 8.5 and below.

Turk: I don't think the the not equal (!=) will work here, they're looking for a nested query function.

-k

 

[JenL]

Thank you everyone. I noticed that I forgot to mention that I am using CR 8.5

I will try to manually edit the SQL... however since we plan on going to a newer version of CR fairly soon I may just wait until then since it seems to be easier with other versions.

Thanks once again!
JenL

 

[synapsevampire]

Don't be a coward, Jen

The downside of manually editing the SQL is that you may lose the ability to use the interface to pass SQL.

Creating a View makes the most sense, try not to put anything into the client (CR) that would require maintenance if the database changes.

If you're using SQL Server, creating Views is pretty easy.

Please consider sharing technical information in your posts rather than text descriptions of a problem.

-k

 

[JenL]

synapsevampire: It's not a matter of being a coward but more matter of time constraints so in my situation right now, a db view is most definitely not an option in our environment (wishful thinking but impossible). Since these types of query are so easily done in SQL, I thought there might be a simple way to do this in CR also.

Once again thanks for all your posts.

Looking forward to "discussing" with you all again in the near future.

JenL