CODEWITHSUNDEEP
mysqlsql
Shaonline
Shaonline

Reputation: 1637

sql union with not in condition

I have this query which is a union of multiple selections based on a condition. The problem is "AND" section which is being applied only to the last selection, but i want to apply it to the whole selection (all the unions)

select * from
(select con.id_contact as  v,substr(concat(ifnull(con.firstname,''),ifnull(con.lastname,'')),1,4) as s0,   null as s1, null as s2 from contact con, fu_rec_group_link g
where con.id_contact = g.id_rec and id_group=125801) as tbl244295 where 1=1   UNION
select * from
(select con.id_contact as v,substr(concat(ifnull(con.firstname,''),ifnull(con.lastname,'')),1,4) as s0, null as s1, null as s2 from contact con, fu_rec_group_link g where
con.id_contact = g.id_rec and id_group=125803) as tbl244296 where 1=1 UNION
select * from
(select con.id_contact as  v,substr(concat(ifnull(con.firstname,''),ifnull(con.lastname,'')),1,4) as s0,  null as s1, null as s2 from contact con, fu_rec_group_link g where
con.id_contact = g.id_rec and id_group=126270) as tbl244297 where 1=1 AND v NOT IN
(select v from (select * from (select con.id_contact as v,substr(concat(ifnull(con.firstname,''),ifnull(con.lastname,'')),1,4) as s0, null as s1, null as s2 from contact con
where id_user=11944 and unregistered is not null) as tbl244299 where 1=1) as tblMinus )

Upvotes: 1

Views: 1943

Answers (2)

jpw
jpw

Reputation: 44881

The fix for your original query is, as pointed out by @jarlh, to wrap the unions in another select and apply the last where condition to that. Also, when you use union without all you eliminate duplicates, but in a way that can be expensive; it might be better to specify union all and use either distinct or a group by clause to eliminate duplicate rows.

But... looking at your query it would seem it can be simplified quite a bit and reduced to the query below, which should render the same results but perform better.

select distinct
    con.id_contact as v, 
    substr(concat(ifnull(con.firstname,''),ifnull(con.lastname,'')),1,4) as s0, 
    null as s1, 
    null as s2 
from contact con
join fu_rec_group_link g on con.id_contact = g.id_rec 
where id_group in (125801, 125803, 126270)
and con.id_contact not in (
    select con.id_contact as v
    from contact con
    where id_user=11944 and unregistered is not null
)

Since there was neither any information about the table designs nor any data to test with my assumptions could be completely incorrect, and if so I'll promptly remove the answer.

Upvotes: 2

jarlh
jarlh

Reputation: 44766

Do the UNIONs in a derived table, put the WHERE outside it:

select * from
(
select ...
union
select ...
) dt
where dt.col ...

Upvotes: 0

Related Questions