Brent Baisley schrieb:
I'll give it a shot.
First, select the people that got the first advertisement:
SELECT c_id,aa_id
FROM adverticelink
WHERE aa_id=4
From that result, you want to additionally filter out who didn't get
the second advertisement. Since that information is contained in the
same table, you want to do a self join. A self join will require you to
use an alias name for the table, since you can't have two tables with
the same name. We'll use a1 and a2 as the alias names.
Additionally, you want to do a left join to retain all the records from
your originally query. So you are actually joining the query of those
who received the first ad, with those who received the second ad. Since
you are doing a left join, those who didn't receive the second ad will
not have a value for the aa_id field. It will be NULL.
SELECT a1.c_id,a1.aa_id,a2.aa_id
FROM adverticelink AS a1
LEFT JOIN adverticelink AS a2 ON (a1.c_id=a2.c_id AND a2.aa_id=6)
WHERE a1.aa_id=4 AND a2.aa_id IS NULL
That should work in 3.23.
Yeah i know where you want to go to.
This looks really good, never thought about rejoining the table.
Thanks for that reply, it shed a light :)
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
