Join of SQL tables

Question

I have a small problem when doing a Join between two tables in SQL, I wonder if you can help me.

Are 2 tables GTM_ITEM_CLASSIFICATION and ITEM_REFNUM, both reference the table ITEM, but using different keys. For the first table, the field GTM_ITEM_GID and for the second table, the field ITEM_GID.

What happens is that when there is one Insert User called "ADMIN" in the first table (GTM_ITEM_GID), I need to recover in the other table which is the record corresponding to the ADMIN. This second table, is an attributes table. For the type of users I want, I need to use a filter ITEM_REFNUM_QUAL_GID = 'PC_CODE_APPROVER'

However, in applying the following select,

SELECT * FROM GTM_ITEM_CLASSIFICATION G
LEFT JOIN ITEM_REFNUM R 
ON G.GTM_ITEM_GID = R.ITEM_GID 
WHERE G.GTM_ITEM_GID IN ('1','2') 
AND R.ITEM_REFNUM_QUAL_GID = 'PC_CODE_APPROVER'

The only record displayed to me is the 1, while I’d like you to exhibit the 2 records (the second with a null reference on the side).

You know if it’s possible to make one select that returns all records, and those that do not exist in the second table return as null to me?!

Answer 1

Your problem seems to me to be in condition after the AND: it will only be true if there are non-zero values in the columns of R. Your Join is correct - were it not for this condition, every row in the first table that had no corresponding in the second would be returned with null values.

My first suggestion is to replace this condition with:

AND (R.ITEM_REFNUM_QUAL_GID = 'PC_CODE_APPROVER' OR R.ITEM_REFNUM_QUAL_GID IS NULL)

I’m not sure if this satisfies your logic, but I have nothing better to suggest - after all, you can only test conditions on the second table if it is not null... (in other words, how will you know if the R.ITEM_REFNUM_QUAL_GID is PC_CODE_APPROVER if there are no lines on R who marry their line in G? This is an impossible condition, if that’s what you’re looking for stop and review your requirements)

Answer 2

You can put any condition for the junction to occur (in this case, the value of ITEM_REFNUM_QUAL_GID, in the ON of your LEFT JOIN:

SELECT * FROM GTM_ITEM_CLASSIFICATION G
LEFT JOIN ITEM_REFNUM R 
    ON G.GTM_ITEM_GID = R.ITEM_GID AND R.ITEM_REFNUM_QUAL_GID = 'PC_CODE_APPROVER'
WHERE G.GTM_ITEM_GID IN ('1','2')