comparing tables in select expert 1

[Jenke]

Hi all..

Using CR9 and ODBC connection to SQL Server 7 database..

I am trying to select values from table A based on values from table B.

example
table B contains values 'monkey', 'dog', 'cat', etc...

i want to select values from table A which are not in that master list of values in table B.

i do not want to use the not in ['monkey', 'dog', 'cat'] method because the list in table B contains hundreds of values which i dont want to type out by hand, or select individually from the list, and it is also quite possible that the values in table B could change down the road and i will need to run this report again.

is there a way to programatically compare table A values against table B values and return those from table A which are not present in table B?

TIA

Jon

 

[synapsevampire]

The simplest and fastest method would be to create a View on the database which does something like:

select blah from tablea where
animal not in (select animal from tableb)

This would provide an enterprise wide data source, and simplify maintenance by keeping the rule out of the client (CR).

Within Crystal 9 you can use the SQL directly as well by using the Add Command function and pasting the SQL in as the data source.

-k

 

[vidru]

You should be able to do it with a left outer join from TableA to TableB. Link {TableA.Field} to {TableB.Field} and set the join type to a Left Outer Join.

In your record selection formula, enter...
[tt]
isnull({TableB.Field})
[/tt]
... to get the TableA values that don't exist in TableB.

If you want to do it with a Command object, something like this will work for SQL Server:
[tt]
SELECT A.Field
FROM TableA A
LEFT OUTER JOIN TableB B ON (A.Field = B.Field)
WHERE B.Field IS NULL
[/tt]
-dave

 

[synapsevampire]

Dave: The problem with the 1st solution is that the table A may have a null value in it's set.

Whereas a NOT IN or NOT EXISTS is explicit.

-k

 

[Jenke]

Thanks for the help guys..

Im not sure if I will be able to do a view yet since that will require getting special permission to the db, and ive yet to hear back about that..

I am trying the 'Add Command' method to select the data source.

here is my sql:

select * from MTIL-CI-ConfigurationItem-200-N where MTIL-CI-ConfigurationItem-200-N.'Product Name' NOT IN (SELECT 'Product Name' FROM MTIL-FN-ProductCatalog-012-N)

I am receiving lexical element errors (=, <>, etc..) missing

i tried the NOT EXISTS too..

its been quite a while since ive done this stuff so it may be something simple that im missing..

hopefully im missing something simple..

thanks again

Jon

 

[vidru]

Try enclosing the table names in brackets. This should work with NOT EXISTS:

SELECT CI.*
FROM [MTIL-CI-ConfigurationItem-200-N] CI
WHERE NOT EXISTS (SELECT [Product Name]
FROM [MTIL-FN-ProductCatalog-012-N]
WHERE [Product Name] = Item.[Product Name])

-dave

 

[Jenke]

Thanks for your help guys

I was able to get in to create a view which worked just great