Raphael Telatim Raphael Telatim - 1 year ago 55
SQL Question

Show on my select result that don't match

I have these three tables:

campaigns

id | name | campaign_strategy_id
----+-------------+----------------------
5 | Teste Diego | 3


campaign_strategy_blocked_routes

campaign_strategy_id | route_id
----------------------+----------
3 | 2
3 | 6


And routes:

name | id
-----------------+----
Vonex Teste | 3
Filial Total IP | 2
Filial TotalIP | 6
Tellfree teste | 5


And i need to check the routes that arent blocked on a campaign. In that case, note that campaign id 5 have two blocked routes: 2 and 6, but I need to show the other two routes on my select: 3 and 5.

I have something like this:

select campaigns.name, routes.id as route_id from campaigns left join campaign_strategy_blocked_routes on campaigns.campaign_strategy_id = campaign_strategy_blocked_routes.campaign_strategy_id left join routes on campaign_strategy_blocked_routes.route_id = routes.id


But the result, obviously is:

name | route_id
-------------+----------
Teste Diego | 2
Teste Diego | 6


And, like i said, what i need is:

name | route_id
-------------+----------
Teste Diego | 3
Teste Diego | 5


Is it possible to do this?

Basically, who wants to play: http://sqlfiddle.com/#!15/f1305/3

Answer Source
select c.name, r.id
from campaigns c 
inner join routes r 
on not exists(select 1 from campaign_strategy_blocked_routes 
                where route_id = r.id 
                and campaign_strategy_id = c.campaign_strategy_id)

Explanation:

Rows from campaigns table and routes table are joined if the subquery doesn't find any matching rows from campaign_strategy_blocked_routes table.

exists returns true if one or more matching rows from campaign_strategy_blocked_routes are found, but since we care about the case where there is no match we negate the expression (i.e not exists(...)).

The subquery is essentially equivalent to

select 1
from campaigns c 
inner join campaign_strategy_blocked_routes csbr
    on csbr.campaign_strategy_id = c.campaign_strategy_id
inner join routes r on csbr.route_id = r.id

So it finds blocked routes for campaigns.
But instead of joining tables in the subquery the values r.id and c.campaign_strategy_id are provided as constants by the outer query.

And select 1 is used because we don't care about returning any particular data from the subquery, we just want to know if there are any rows to return.

Documentation about exists

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download