Findout Unmatched rows without unique value

[punna111]

Hi All,

I have Table1 as below:

Inv_No.	Amt
555	100
555	50
555	200
555	50
555	300
555	50
555	50

and Below is the Table 2

Inv_No.	Amt
555	50
555	200
555	50

I would like to find out Unmatched rows, as like below:

Inv_No.	Amt
555	100
555	300
555	50
555	50

Can any help me to get the above unmatched rows
Thanks in advance

[JoeM]

Do an Unmatched Query, but matching on two fields instead of one.

SELECT Table1.[Inv_No], Table1.[Amt]
FROM Table1
LEFT JOIN Table2
ON Table1.[Inv_No]=Table2.[Inv_No] AND Table1.[Amt]=Table2.[Amt]
WHERE Table2.[Inv_No] Is Null;

[punna111]

Hi JoeM,

Thanks you for reply..

But I am getting the result as below. any more help please..

Inv_No	Amt
555	100
555	300

[CJ_London]

you need something more in your data - inv 555, amt 50 appears a number of times in your tables so all match. The something needs to be a unique value of some sort. Even then I'm not sure you can do it

databases have no sense of order so you cannot say 'the first 555/50 in table2 matches to the first 555/50 in table1 and the second to the second and that leaves the 3rd and 4th unmatched'