Join SQL tables

Question

Join SQL tables

Navigation

#1 by (1 votes)
#2 by (0 votes)

4

I'm having a small problem making a Join between two tables in SQL, I'd like to know if you can help me.

There are 2 tables GTM_ITEM_CLASSIFICATION and ITEM_REFNUM , both refer to the ITEM table, but using different keys. For the first table, the GTM_ITEM_GID field is used and the ITEM_GID field is used for the second table.

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

However, when you apply 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 I see is 1 , whereas I'd like it to display the 2 records (the second with a null reference next to it).

Do you know if it is possible to make a select that returns all records, and those that do not exist in the second table return as null to me?!

sql oracle

asked by anonymous 03.07.2014 / 14:24

2 answers

Syntactic Reader (Visual Studio 2013 Extension) Display all tables in the PHPMyAdmin navigation column

score 1 · Answer 1

Your problem seems to be in the condition after AND : it will only be true if there are non-null values in the R columns. Your join is correct - if it were not for this condition, every row in the first table that did not have 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 in the second table if it is not null ... (in other words, how will you know if%

score 0 · Answer 2

You can place any condition for the join to occur (in this case, the value of ITEM_REFNUM_QUAL_GID , in 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')