CODEWITHSUNDEEP
sqlsql-servert-sqlleft-join
Kaung Htet
Kaung Htet

Reputation: 11

How to change this 'Not In' Query to Left Join query

I've been trying to change this query. I don't want to use 'Not In' in this query. Can anybody help me how to change this query to left join query?

SELECT t.date,t.ticket,t.weight,t.Count, td.description
FROM tblticket t inner join tblticketdetails td on t.ticket = td.ticket
WHERE t.ticket NOT IN (SELECT r.Original_ticket from tblRenew R where isnull(r.new_ticket,'') = '' and r.transaction_status = 'valid') 
AND RTRIM(LTRIM(t.status)) = 'OPEN'
AND td.Type = 62
AND t.weight/t.Count >= 1000
AND t.date BETWEEN '2011-12-31' AND '2013-01-17'

Upvotes: 1

Views: 303

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

SELECT t.date,  t.ticket,t.weight, t.Count, td.description
FROM tblticket t inner join 
     tblticketdetails td
     on t.ticket = td.ticket left outer join
     (SELECT r.Original_ticket
      from tblRenew R
      where isnull(r.new_ticket,'') = '' and
      r.transaction_status = 'valid'
     ) v
     on t.ticket = v.Original_ticket
WHERE t.ticket NOT IN (SELECT r.Original_ticket from tblRenew R where isnull(r.new_ticket,'') = '' and r.transaction_status = 'valid') 
AND RTRIM(LTRIM(t.status)) = 'OPEN'
AND td.Type = 62
AND t.weight/t.Count >= 1000
AND t.date BETWEEN '2011-12-31' AND '2013-01-17'
and v.original_tiket is null

Upvotes: 1

Guffa
Guffa

Reputation: 700172

Providing that you don't have multiple records in tblRenew for any ticket:

select
  t.date, t.ticket, t.weight, t.Count, td.description
from
  tblticket t
  inner join tblticketdetails td on t.ticket = td.ticket
  left join tblRenew R on isnull(r.new_ticket,'') = '' and r.transaction_status = 'valid' and r.Original_ticket = t.ticket
where
  r.Original_ticket is not null
  and RTRIM(LTRIM(t.status)) = 'OPEN'
  and td.Type = 62
  and t.weight/t.Count >= 1000
  and t.date BETWEEN '2011-12-31' AND '2013-01-17'

Upvotes: 1

Related Questions