[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Problem searching in grouped rows
From:       84.129.248.53
Date:       2006-06-29 7:35:20
Message-ID: 20060629073328.26501.qmail () lists ! mysql ! com
[Download RAW message or body]

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/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]