Querying tables with 2 primary keys and index

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a query that works, but I would like to know:
A. why it works
B. is there a better way

(Invoice_Number and Line = unique identifier.)

SUB_1 table MASTER table
Invoice Line Invoice Line
a1231 1 a1231 1
a1231 5 a7897 1
a7897 1 a1231 2
b4556 1

My Query:

SELECT SUB_1.Invoice_Number, SUB_1.Line
FROM SUB_1
WHERE SUB_1.LINE<>ALL(SELECT MASTER.Line FROM MASTER WHERE
MASTER.Invoice_Number=ALL(SELECT SUB_1.Invoice_Number FROM MASTER));

DESIRED RESULTS- would be for the query to return every Invoice_Number and
Line in the SUB_1 table that do NOT have a matching set (Invoice and Line) in
the MASTER table.

Recordset
Invoice Line
a1231 5
b4556 1

My query gives me the desired results, but to me, it sure doesn't look like
it should even work. Especially given that last inner query
"SELECT SUB_1.Invoice_Number FROM MASTER"

Please advise.

Thanks in Advance-
 
I think you could get the desired results quicker with

SELECT S.*
FROM Sub_1 as S LEFT JOIN Master as M
ON S.Invoice_Number = M.Invoice_Number
AND S.Line = M.Line
WHERE M.Invoice_Number is Null

I'm not sure why your method works.
 
Thank you!

John Spencer (MVP) said:
I think you could get the desired results quicker with

SELECT S.*
FROM Sub_1 as S LEFT JOIN Master as M
ON S.Invoice_Number = M.Invoice_Number
AND S.Line = M.Line
WHERE M.Invoice_Number is Null

I'm not sure why your method works.
Click to expand...
 
Back
Top