List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:January 31 2006 7:26pm
Subject:Re: NOT IN vs IS NULL
View as plain text  
Devananda,

OK, get the diescription in ordinary language right, and the query ought 
to write itself. Your schema is like this? ...

model

and you want the paytable rows ....
  (i) for which there is an offers row matching paytable.offer_id, and
  (ii) for which there is no pab row where 
pab.login_id=paytable.login_id and pab.advertiser_id=offer_id?

Would that be ...

SELECT pt.offer_id, pt.login_id, o.advertiser_id
FROM paytable AS pt
INNER JOIN offers AS o USING (offer_id)
LEFT JOIN publisher_advertiser_blocks AS pab1 ON pt.login_id=pab1.login_id
LEFT JOIN publisher_advertiser_blocks AS pab2 ON 
o.advertiser_id=pab2.advertiser_id
WHERE pab1.login_id IS NULL OR pab2.advertiser_id IS NULL;

PB


Attachment: [text/html]
Attachment: [image/jpeg] SP32-20060131-124913.jpg
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.25/247 - Release Date: 1/31/2006 --=======AVGMAIL-43DFB9D70378=======--
Thread
NOT IN vs IS NULLDevananda27 Jan
  • Re: NOT IN vs IS NULLPeter Brawley28 Jan
Re: NOT IN vs IS NULLPeter Brawley31 Jan
Re: NOT IN vs IS NULLPeter Brawley1 Feb
  • Re: NOT IN vs IS NULLSGreen1 Feb
    • Re: NOT IN vs IS NULLDevananda1 Feb
      • Re: NOT IN vs IS NULLSGreen1 Feb
        • Re: NOT IN vs IS NULLDevananda2 Feb