Re: Problem searching in grouped rows
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]
Re: Problem searching in grouped rows
Barry schrieb: Hello everyone! I have a problem with matching in grouped rows. I have: - one DB with customers - one DB with advertisement articles - one DB that holds what customer got which article the linked DB looks like: CREATE TABLE adverticlelink ( c_id int(11) NOT NULL, aa_id int(11) NOT NULL, recieved date NOT NULL, PRIMARY KEY (k_id,ml_id) ) ENGINE=MyISAM; ++-+---+ |c_id|aa_id|recieved | ++-+---+ |4 |2|48642465464| |4 |6|35465432234| |4 |15 |31354513213| ++-+---+ I want now to match customers that got for example the advertisement umbrella but not the advertisement zippo. No idea how to start that query. On top of that is use the MySQL Version 3.23.54, for pc-linux (i686). Anyone with any ideas? I did try it with WHERE (aa_id = 4) AND (aa_id != 6) GROUP BY c_id Also tried the HAVINg clause but that looked really false. Thanks for any replies :) Barry Noone? :( -- 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]
Re: Problem searching in grouped rows
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. - Original Message - From: Barry [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, June 26, 2006 3:58 AM Subject: Re: Problem searching in grouped rows Barry schrieb: Hello everyone! I have a problem with matching in grouped rows. I have: - one DB with customers - one DB with advertisement articles - one DB that holds what customer got which article the linked DB looks like: CREATE TABLE adverticlelink ( c_id int(11) NOT NULL, aa_id int(11) NOT NULL, recieved date NOT NULL, PRIMARY KEY (k_id,ml_id) ) ENGINE=MyISAM; ++-+---+ |c_id|aa_id|recieved | ++-+---+ |4 |2|48642465464| |4 |6|35465432234| |4 |15 |31354513213| ++-+---+ I want now to match customers that got for example the advertisement umbrella but not the advertisement zippo. No idea how to start that query. On top of that is use the MySQL Version 3.23.54, for pc-linux (i686). Anyone with any ideas? I did try it with WHERE (aa_id = 4) AND (aa_id != 6) GROUP BY c_id Also tried the HAVINg clause but that looked really false. Thanks for any replies :) Barry Noone? :( -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem searching in grouped rows
Hello everyone! I have a problem with matching in grouped rows. I have: - one DB with customers - one DB with advertisement articles - one DB that holds what customer got which article the linked DB looks like: CREATE TABLE adverticlelink ( c_id int(11) NOT NULL, aa_id int(11) NOT NULL, recieved date NOT NULL, PRIMARY KEY (k_id,ml_id) ) ENGINE=MyISAM; ++-+---+ |c_id|aa_id|recieved | ++-+---+ |4 |2|48642465464| |4 |6|35465432234| |4 |15 |31354513213| ++-+---+ I want now to match customers that got for example the advertisement umbrella but not the advertisement zippo. No idea how to start that query. On top of that is use the MySQL Version 3.23.54, for pc-linux (i686). Anyone with any ideas? I did try it with WHERE (aa_id = 4) AND (aa_id != 6) GROUP BY c_id Also tried the HAVINg clause but that looked really false. Thanks for any replies :) 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]
