Sivard Sivard - 2 years ago 81
SQL Question

left join where b.key is null by two columns

I have two tables.

table_1 | table_2
id col_1 | col_2 | col_1 | col_2
1 1 | B | 1 | B
2 1 | C | 3 | C
3 1 | D | 5 | D

I write this query

FROM table_1 t1
LEFT JOIN table_2 t2 ON t1.col_1 = t2.col_1
WHERE t1.col_1 = 1
t2.col_1 IS NULL
AND t2.col_1 != 'B'

I want get this result.

table_1 |
id col_1 | col_2 |
2 1 | C |
3 1 | D |

How can I do that? Thanks!

Update question

Table 1 - PriceItems, Table 2 - BlockedPrices, col1 - Code, col2 - Brand.

I want to get all PriceItems current code and all brands exept codes from BlockesPrices.

Answer Source

I think this is what you're looking for:

Select      t1.*
From        table_1 t1
Left Join   table_2 t2  On  t1.col_1 = t2.col_1
                        And t2.col_1 != 'B'
Where       t1.col_1 = 1
And         t2.col_1 Is Not Null

As I understand your question, you're looking to do a LEFT JOIN on the second table, but have the case where t2.col_1 = 'B' excluded from your final result.

In this case, you'll need to move the condition t2.col_1 = 'B' to your ON clause to have records that don't match both of the LEFT JOIN conditions come back as NULL.

The reason you might have seen strange results is due to the AND t2.col_1 != 'B' condition in your WHERE clause. This causes the filter to happen after the LEFT JOIN, which will further filter those results (thus, effectively transforming the LEFT JOIN into an INNER JOIN.

Edit, question changed:

Based on the new information in the question, you are using the second table as an exclusion table, and want to remove the results from the first table that appear in the second.

For this, you don't need a JOIN at all, you can just do it with a WHERE NOT EXISTS as follows:

Select  *
From    PriceItems  P
Where Not Exists
    Select  *
    From    BlockedPrices   B
    Where   P.Code = B.Code
    And     P.Brand = B.Brand
And     P.Code = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download