values not in table

[mildoo]

I have a table (table1) with one of the fields storing numbers this is joined to another table (table2) which also has a field storing numbers. What I want to do is display the numbers from table1 which are not matched with the numbers in table2.

Any suggestions?

 

[Katy44]

link table 1 to table 2 on that field using a left join.



In the record selection criteria put:

where isNull({table2.fieldname})

Hope this has worked

 

[mildoo]

Thanks katy44. This is all new to me. I am using crystal reports 10 and an Oracle db. The two tables I am using are called submission and match. The submission table holds all application numbers which have been sent to our system. The match table holds all the application numbers which have matched with each other known as Left hand side and right hand side matches. What I need to do is look at the numbers from both the left and right hand side and if these numbers are not matched with the application numbers in the submission table, then display the unmatched application numbers from the submission table.

It is difficult to explain...

 

[Katy44]

Can you access the database? For example to create a view?
What fields does the match table have in it? Are 'left hand side' and 'right hand side' two fields in that table?

You want all the numbers from the submission table, that do not appear as either LHS or RHS numbers in the match table- is that right?

 

[mildoo]

I can do some SQL in Crystal 10
Yes the LHS and RHS fields are in the same table. Yes, I want all the numbers from the submission table that do not appear in the LHS or RHS fields.

 

[Katy44]

I meant can you create a view in the database?
I can't see a way to do this using left joins, although there may be a way. If that was going to work, you would need to bring another copy of the match table into your report.

 

[mildoo]

Yes I can create a view in the Oracle Db.