November 6, 2008 at 7:21 am
Hello
How I can replace an 'NOT IN' statement with a left join statement
select t.CodClient,t.Name, (t.Address2 + t.Address3) AS Address
from CUSTOMERS t
inner join ikCUSTOMERS ikC on ikC.Id = t.CodClient
WHERE t.CodClient NOT IN
(SELECT m.CustomerId FROM ikINCOMING_MESSAGES m
WHERE m.MsgType = 7)
Thanks
November 6, 2008 at 7:34 am
I think something like this should do. Use a derived table and then left join with a where null condition to find records not joined.
select t.CodClient,t.Name, (t.Address2 + t.Address3) AS Address
from CUSTOMERS t
inner join ikCUSTOMERS ikC on ikC.Id = t.CodClient
left outer join
(SELECT m.CustomerId
FROM ikINCOMING_MESSAGES m
WHERE m.MsgType = 7) tblX ON
(t.CodClient = tblx.customerid)
where tblx.customerid is null
November 6, 2008 at 7:41 am
Thanks a lot
November 6, 2008 at 11:53 am
This should work, if I followed your example correctly:
select t.CodClient,t.Name, (t.Address2 + t.Address3) AS Address
from CUSTOMERS t
inner join ikCUSTOMERS ikC on ikC.Id = t.CodClient
left outer join ikINCOMING_MESSAGES m on t.CodClient = m.CustomerId
and m.MsgType = 7
where m.CustomerId IS NULL
The more you are prepared, the less you need it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply