Table_A has one row where WO_ID = ‘123456’

Table_B has two rows where WO_ID = ‘123456’.
     The difference is Table_B.Order_Line_No = ‘1’ and ‘2’

Consequently my report shows 2 lines of data when only 1 is required.

I could do a row count and select on 1 only, but can’t you join them to obtain the results you want?

How do I join the tables to include everything in Table_A and only one instance of Table_B

If Left Outer results contains All A and the matching in B
and Right Outer results contains All B and the matching in A
how does either of these eliminate the second line in Table_B

PS. and when you join, do you have to change the link on every linked field or just one?

Table_B.Order_Line_No isn't only 1 or 2, it might be 78 or 53.
The remaining fields either contain the same data or are not a known entity 
I could select on.

I could use a SQL command to return the min row only but SQL commands slow down my reports so much it is unbearable for the user.

Views??? still waiting on "IT" to grant me permission to create - this would solve a lot a my frustrations!

Don't know what a stored procedure means.

For now, I'll just add a row count and suppress >1

edit - well I added the row count & suppressed but the summary total of the field still includes ll the suppressed data...so that doesn't work!


Edited by carstowal - 01 Jun 2009 at 10:42am