group by/select issue..
hi... i have the following test tbl dog name char statusint idint test data dog name status id tom 1 1 tom 2 2 sue 1 3 tom 3 4 sue 2 5 bob 1 6 i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' i'd also like to be able to get a single row for 'bob' if i wanted the 'name' (group) that do not have a status=2. i'm not sure how to craft the select using the group by/distinct, and i couldn't find examples via google to solve this... once i get my hands around this, i can apply it to a test tbl of 2000-3000 rows... thanks bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by/select issue..
bruce wrote: i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` wouldn't give you what you'd want (or that's possibly what you're looking for?). If that's the answer then wee, if not I'll throw my lost flag in the air. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by/select issue..
hi chris... your query, SELECT name FROM dog WHERE status = 3 GROUP BY name will actually give the items where status=3 however, i can't get the resulting issues by doing 'status!=3', because the tbl has multiple status for a given name, so the query will still return the other status that aren't equal to '3' for the given name... -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:07 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: group by/select issue.. bruce wrote: i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` wouldn't give you what you'd want (or that's possibly what you're looking for?). If that's the answer then wee, if not I'll throw my lost flag in the air. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by/select issue..
Bruce, Try: SELECT DISTINCT NAME FROM DOG WHERE STATUS != 3 Should do the trick. You obviously don't want the STATUS field. If you include it, you'll get more than one line per name. Similarly for ID. If you want to include the STATUS or ID fields, then you obviously want more than one line (otherwise what would you expect to go in there?). HTH Peter Ysgrifennodd bruce: hi chris... your query, SELECT name FROM dog WHERE status = 3 GROUP BY name will actually give the items where status=3 however, i can't get the resulting issues by doing 'status!=3', because the tbl has multiple status for a given name, so the query will still return the other status that aren't equal to '3' for the given name... -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:07 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: group by/select issue.. bruce wrote: i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` wouldn't give you what you'd want (or that's possibly what you're looking for?). If that's the answer then wee, if not I'll throw my lost flag in the air. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by/select issue..
hi peter i must be missing something. the following is my actual schema. i have a test tbl with ~2900 rows... only a few of the rows have an actionID=3. each universityID can have multiple actionIDs mysql describe SvnTBL; +--+--+--+-+++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+---++ | universityID | int | NO | | 0 || | actionID | int | NO | | 0 || | statusID | int | NO | | 0 || | _date| timestamp| YES | | CURRENT_TIMESTAMP | | | ID | int | NO | PRI | NULL | auto_increment | | semseterID | int | NO | | 0 || +--+---+--+-+---++ 6 rows in set (0.09 sec) when i do: select distinct universityID, from SvnTBL where actionID !=3; i get return of 2879 rows, which is the same thing i get when i do: select distinct universityID, from SvnTBL; when i do: mysql select universityID, actionID from SvnTBL - where actionID =3; +--+--+ | universityID | actionID | +--+--+ |1 |3 | |2 |3 | |3 |3 | +--+--+ 3 rows in set (0.00 sec) which tells me that i have 3 'groups' (on universityID) that have actionID=3. however, each of these universityID, can also have actionID=(1,2) as well. so how can a query be created to return the universityID (groups) that don't have an actionID=3... when i tried, SELECT DISTINCT universityID FROM SvnTBL WHERE actionID != 3 i got the same as if i did: SELECT DISTINCT universityID FROM SvnTBL; thanks.. -Original Message- From: Peter Bradley [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:32 AM To: [EMAIL PROTECTED] Cc: 'Chris White'; mysql@lists.mysql.com Subject: Re: group by/select issue.. Bruce, Try: SELECT DISTINCT NAME FROM DOG WHERE STATUS != 3 Should do the trick. You obviously don't want the STATUS field. If you include it, you'll get more than one line per name. Similarly for ID. If you want to include the STATUS or ID fields, then you obviously want more than one line (otherwise what would you expect to go in there?). HTH Peter Ysgrifennodd bruce: hi chris... your query, SELECT name FROM dog WHERE status = 3 GROUP BY name will actually give the items where status=3 however, i can't get the resulting issues by doing 'status!=3', because the tbl has multiple status for a given name, so the query will still return the other status that aren't equal to '3' for the given name... -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:07 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: group by/select issue.. bruce wrote: i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` wouldn't give you what you'd want (or that's possibly what you're looking for?). If that's the answer then wee, if not I'll throw my lost flag in the air. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by/select issue..
Use a derived table (untested query): select distinct universityID from SvnTBL s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.university ID is NULL I'm not sure if derived tables are in all versions of MySQL, I use MySQL 5.0. If your tables are big, you'll probably need to add an index on universityID. Hope that helps. Donna bruce [EMAIL PROTECTED] 01/04/2007 12:49 PM Please respond to [EMAIL PROTECTED] To 'Peter Bradley' [EMAIL PROTECTED] cc 'Chris White' [EMAIL PROTECTED], mysql@lists.mysql.com Subject RE: group by/select issue.. hi peter i must be missing something. the following is my actual schema. i have a test tbl with ~2900 rows... only a few of the rows have an actionID=3. each universityID can have multiple actionIDs mysql describe SvnTBL; +--+--+--+-+++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+---++ | universityID | int | NO | | 0 || | actionID | int | NO | | 0 || | statusID | int | NO | | 0 || | _date| timestamp| YES | | CURRENT_TIMESTAMP | | | ID | int | NO | PRI | NULL | auto_increment | | semseterID | int | NO | | 0 || +--+---+--+-+---++ 6 rows in set (0.09 sec) when i do: select distinct universityID, from SvnTBL where actionID !=3; i get return of 2879 rows, which is the same thing i get when i do: select distinct universityID, from SvnTBL; when i do: mysql select universityID, actionID from SvnTBL - where actionID =3; +--+--+ | universityID | actionID | +--+--+ |1 |3 | |2 |3 | |3 |3 | +--+--+ 3 rows in set (0.00 sec) which tells me that i have 3 'groups' (on universityID) that have actionID=3. however, each of these universityID, can also have actionID=(1,2) as well. so how can a query be created to return the universityID (groups) that don't have an actionID=3... when i tried, SELECT DISTINCT universityID FROM SvnTBL WHERE actionID != 3 i got the same as if i did: SELECT DISTINCT universityID FROM SvnTBL; thanks.. -Original Message- From: Peter Bradley [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:32 AM To: [EMAIL PROTECTED] Cc: 'Chris White'; mysql@lists.mysql.com Subject: Re: group by/select issue.. Bruce, Try: SELECT DISTINCT NAME FROM DOG WHERE STATUS != 3 Should do the trick. You obviously don't want the STATUS field. If you include it, you'll get more than one line per name. Similarly for ID. If you want to include the STATUS or ID fields, then you obviously want more than one line (otherwise what would you expect to go in there?). HTH Peter Ysgrifennodd bruce: hi chris... your query, SELECT name FROM dog WHERE status = 3 GROUP BY name will actually give the items where status=3 however, i can't get the resulting issues by doing 'status!=3', because the tbl has multiple status for a given name, so the query will still return the other status that aren't equal to '3' for the given name... -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:07 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: group by/select issue.. bruce wrote: i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` wouldn't give you what you'd want (or that's possibly what you're looking for?). If that's the answer then wee, if not I'll throw my lost flag in the air. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent
RE: group by/select issue..
thanks for the derived tbl approach. it solved my 1st problem/issue. the final query that i used is: select distinct s1.universityID from SvnTBL as s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.universityID is null; this works, in that i get the unique universityID data... i now have two additional questions... 1) in the SvnTBL, how can i also get the actionID value? if i attempt to do something like: select distinct s1.universityID, s1.actionID from SvnTBL as s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ONs1.universityID=s2.universityID where s2.universityID is null group by universityID; the query eventually returns with what appears to be the correct information. i get a distinct universityID/actionID, but the query takes ~65 secs to run... the tbl only has ~2900 rows... 2) also, if i want to do a join with another tbl, where i also want to have the select pull information from the joined tbl, is there a 'better' way to handle this... the 2nd tbl is: mysql describe universityTBL; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | name | varchar(75) | NO | UNI | NULL|| | svn_dir_name | varchar(50) | NO | | NULL|| | city | varchar(20) | YES | | NULL|| | stateVAL | varchar(5) | NO | | NULL|| | userID| int(10) | NO | | 0 || | ID| int(10) | NO | PRI | NULL| auto_increment | | parsefilename | varchar(50) | NO | | NULL|| | statusID | int(1) | NO | | 1 || +---+-+--+-+-++ 8 rows in set (0.01 sec) the join would take place on SvnTBL.universityID=universityTBL.ID thanks for helping me to see what's going on... my initial approach is to simply do the unique select on only the SvnTBL, and then have an iterative loop through the resulting data, where i then query the universityTBL each time... however, this results in the app having to hit the db a number of times... thoughts/comments/ thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 10:01 AM To: [EMAIL PROTECTED] Cc: 'Chris White'; mysql@lists.mysql.com; 'Peter Bradley' Subject: RE: group by/select issue.. Use a derived table (untested query): select distinct universityID from SvnTBL s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.university ID is NULL I'm not sure if derived tables are in all versions of MySQL, I use MySQL 5.0. If your tables are big, you'll probably need to add an index on universityID. Hope that helps. Donna bruce [EMAIL PROTECTED] 01/04/2007 12:49 PM Please respond to [EMAIL PROTECTED] To 'Peter Bradley' [EMAIL PROTECTED] cc 'Chris White' [EMAIL PROTECTED], mysql@lists.mysql.com Subject RE: group by/select issue.. hi peter i must be missing something. the following is my actual schema. i have a test tbl with ~2900 rows... only a few of the rows have an actionID=3. each universityID can have multiple actionIDs mysql describe SvnTBL; +--+--+--+-+++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+---++ | universityID | int | NO | | 0 || | actionID | int | NO | | 0 || | statusID | int | NO | | 0 || | _date| timestamp| YES | | CURRENT_TIMESTAMP | | | ID | int | NO | PRI | NULL | auto_increment | | semseterID | int | NO | | 0 || +--+---+--+-+---++ 6 rows in set (0.09 sec) when i do: select distinct universityID, from SvnTBL where actionID !=3; i get return of 2879 rows, which is the same thing i get when i do: select distinct universityID, from SvnTBL; when i do: mysql select universityID, actionID from SvnTBL - where actionID =3; +--+--+ | universityID | actionID | +--+--+ |1 |3 | |2 |3 | |3 |3 | +--+--+ 3 rows in set (0.00 sec) which tells me that i have 3 'groups' (on universityID) that have actionID=3. however, each of these universityID, can also have actionID=(1,2) as well. so how can a query
RE: group by/select issue..
select s1.universityID, s1.actionID, ut.svn_dir_name (or other columns of your choice) from from SvnTBL as s1 inner join universityTBL ut on s1.univeristyID=ut.ID left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.universityID is null; I think you should put indexes, if you don't already have them, on SvnTBL.universityID and universityTBL.id. You also shouldn't need the group by you have in your first query below, but I can't tell you if that is hurting performance. Try putting EXPLAIN in front of the query and it'll give you some details of the query plan. Donna bruce [EMAIL PROTECTED] 01/04/2007 01:45 PM Please respond to [EMAIL PROTECTED] To [EMAIL PROTECTED] cc mysql@lists.mysql.com Subject RE: group by/select issue.. thanks for the derived tbl approach. it solved my 1st problem/issue. the final query that i used is: select distinct s1.universityID from SvnTBL as s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.universityID is null; this works, in that i get the unique universityID data... i now have two additional questions... 1) in the SvnTBL, how can i also get the actionID value? if i attempt to do something like: select distinct s1.universityID, s1.actionID from SvnTBL as s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ONs1.universityID=s2.universityID where s2.universityID is null group by universityID; the query eventually returns with what appears to be the correct information. i get a distinct universityID/actionID, but the query takes ~65 secs to run... the tbl only has ~2900 rows... 2) also, if i want to do a join with another tbl, where i also want to have the select pull information from the joined tbl, is there a 'better' way to handle this... the 2nd tbl is: mysql describe universityTBL; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | name | varchar(75) | NO | UNI | NULL|| | svn_dir_name | varchar(50) | NO | | NULL|| | city | varchar(20) | YES | | NULL|| | stateVAL | varchar(5) | NO | | NULL|| | userID| int(10) | NO | | 0 || | ID| int(10) | NO | PRI | NULL| auto_increment | | parsefilename | varchar(50) | NO | | NULL|| | statusID | int(1) | NO | | 1 || +---+-+--+-+-++ 8 rows in set (0.01 sec) the join would take place on SvnTBL.universityID=universityTBL.ID thanks for helping me to see what's going on... my initial approach is to simply do the unique select on only the SvnTBL, and then have an iterative loop through the resulting data, where i then query the universityTBL each time... however, this results in the app having to hit the db a number of times... thoughts/comments/ thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 10:01 AM To: [EMAIL PROTECTED] Cc: 'Chris White'; mysql@lists.mysql.com; 'Peter Bradley' Subject: RE: group by/select issue.. Use a derived table (untested query): select distinct universityID from SvnTBL s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.university ID is NULL I'm not sure if derived tables are in all versions of MySQL, I use MySQL 5.0. If your tables are big, you'll probably need to add an index on universityID. Hope that helps. Donna bruce [EMAIL PROTECTED] 01/04/2007 12:49 PM Please respond to [EMAIL PROTECTED] To 'Peter Bradley' [EMAIL PROTECTED] cc 'Chris White' [EMAIL PROTECTED], mysql@lists.mysql.com Subject RE: group by/select issue.. hi peter i must be missing something. the following is my actual schema. i have a test tbl with ~2900 rows... only a few of the rows have an actionID=3. each universityID can have multiple actionIDs mysql describe SvnTBL; +--+--+--+-+++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+---++ | universityID | int | NO | | 0 || | actionID | int | NO | | 0 || | statusID | int | NO | | 0 || | _date| timestamp| YES | | CURRENT_TIMESTAMP | | | ID | int | NO | PRI | NULL | auto_increment | | semseterID | int
