Try the following:
SELECT leads.* FROM leads
LEFT JOIN blacklist ON leads.refnumberid = blacklist.leadid
WHERE blacklist.leadid IS NULL
AND ...
The LEFT JOIN will give you a row for every row in leads; blacklist columns
will be null if there's no matching blacklist, so the WHERE clause will
filter these out.
Check out LEFT [OUTER] JOIN in the manual...
> From: <[EMAIL PROTECTED]>
> To: "Mysql. Com" <[EMAIL PROTECTED]>
> Subject: sub selects
> Date: Mon, 22 Apr 2002 15:34:22 +0100
>
> I know that mysql does not support sub selects but I want to be able to do
> the following:
>
> I have a table with all my leads in called leads. The table has a unique
ref
> for each lead called refnumberid
> I have a table with leads in and products that leads have asked not to be
> mailed on the table has a field called leadid which stores the value of
> refnumberid above.
>
> I want to select records from the table leads provided they do not have a
> record in the table blacklist. I have used the following select statement
> which produces no results. The intro screen collects the value of adate
and
> acountry
>
> sql2=" SELECT leads.* from leads,blacklist WHERE leads.date1stcontact
<=
> '"&adate&"' and leads.country = '"&acountry&"' and leads.refnumberid <>
> blacklist.leadid and leads.productname <> blacklist.product OR
> leads.date1stcontact <= '"&adate&"' and leads.refnumberid <>
> blacklist.leadid and leads.productname <> blacklist.product and
> '"&acountry&"' = 'ALL'"
> set rs22=Server.CreateObject("ADODB.Recordset")
> rs22.open sql2,db,0,1
>
>
> Can anyone help?
>
> Regards
>
> Michael Johnson
> Managing Director
> Humphrey Consulting Limited BPEnet
> 13 Austin Friars
> London
> EC2N 2JX
> Tel +44(0)1323 438975
> Fax +44(0)1323 738355
> Email [EMAIL PROTECTED]
> URL www.bpenet.net
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
