Lee Lee - 1 year ago 105
SQL Question

SQL Join / WHERE issue

I've been looking but can't find an answer to this so I'm hoping someone can help. I have two tables

Table 1 (audited items)

audit_session_id asset_number
Audit1 1
Audit1 2
Audit2 3

Table 2 (asset)

asset_number location<br>
1 15
2 10
3 15

What I want is a table of assets that appear in Table 2 but not in Table 1 for a given location and audit_session_id. What I have is:

SELECT a.asset_number
FROM auditeditems ai
RIGHT JOIN asset a
on ai.asset_number = a.asset_number
WHERE ai.asset_number IS NULL
AND a.location_id=15;

However I can't filter on audit_session_id as it is NULL. So I get the result:

1,3 when it should just be 1. (assuming I was looking at the audit1 session)

I keep thinking this should be straight forward but can't see where I'm going wrong. Thanks in Advance

Answer Source

I believe you want to put the condition in the ON clause:

SELECT a.asset_number 
FROM auditeditems ai 
     asset a LEFT JOIN
     auditeditems ai
     ON ai.asset_number = a.asset_number AND
        ai.audit_session_id = 'Audit1'
WHERE ai.asset_number IS NULL AND a.location_id = 15;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download