Raphael Telatim Raphael Telatim - 4 months ago 6
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
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

Comments