Re: Query question
Jeff McKeon [EMAIL PROTECTED] wrote on 04/25/2005 03:00:55 PM: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff This is a FAQ. What you have is the groupwise maximum problem described here: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html Except in your case you are not looking for max price for a dealer, you are looking for the latest date for a given parentid. Let us know if you need more details and someone on the list will be happy to help!! :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Query question
Hi, You can do something like that : mysql select * from son; +--+ | a| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.02 sec) mysql select * from mother; +--+--+ | a| b| +--+--+ |1 | a| |1 | b| |2 | a| |2 | c| |3 | a| |3 | b| |3 | c| |3 | d| +--+--+ 8 rows in set (0.00 sec) mysql select a,max(b) from mother - group by a; +--++ | a| max(b) | +--++ |1 | b | |2 | c | |3 | d | +--++ 3 rows in set (0.00 sec) The max will be used with your datetime column. The son table can not be used, or joined to the mother. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 21:01 To: mysql@lists.mysql.com Subject: Query question I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- 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]
RE: Query question
mathias fatene [EMAIL PROTECTED] wrote on 04/25/2005 03:19:33 PM: Hi, You can do something like that : mysql select * from son; +--+ | a| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.02 sec) mysql select * from mother; +--+--+ | a| b| +--+--+ |1 | a| |1 | b| |2 | a| |2 | c| |3 | a| |3 | b| |3 | c| |3 | d| +--+--+ 8 rows in set (0.00 sec) mysql select a,max(b) from mother - group by a; +--++ | a| max(b) | +--++ |1 | b | |2 | c | |3 | d | +--++ 3 rows in set (0.00 sec) The max will be used with your datetime column. The son table can not be used, or joined to the mother. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 21:01 To: mysql@lists.mysql.com Subject: Query question I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff I think I am decent at what I do and that confused even me. I am totally baffled at what SQL concept you were trying to illustrate. How did you _help_ the OP? The question that started this thread is an example of a common class of SQL problems and several solutions exist. Your solution neither answered his query nor was it explained to the point that made it comprehendable. Please, please try to be less confusing (especially when responding to newbies). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Query question
Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Hi, Why my answer doesn't answer his question. Did you heared about his comment. Let him do it. If you're confused, i can explain more one-to-many relashionships. If you think about joins and want absolutely add them, this is the error generating performance problems asked along all RDMBS, especially with mysql (DBMS till now). Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 21:44 To: mathias fatene Cc: 'Jeff McKeon'; mysql@lists.mysql.com Subject: RE: Query question mathias fatene [EMAIL PROTECTED] wrote on 04/25/2005 03:19:33 PM: Hi, You can do something like that : mysql select * from son; +--+ | a| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.02 sec) mysql select * from mother; +--+--+ | a| b| +--+--+ |1 | a| |1 | b| |2 | a| |2 | c| |3 | a| |3 | b| |3 | c| |3 | d| +--+--+ 8 rows in set (0.00 sec) mysql select a,max(b) from mother - group by a; +--++ | a| max(b) | +--++ |1 | b | |2 | c | |3 | d | +--++ 3 rows in set (0.00 sec) The max will be used with your datetime column. The son table can not be used, or joined to the mother. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 21:01 To: mysql@lists.mysql.com Subject: Query question I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff I think I am decent at what I do and that confused even me. I am totally baffled at what SQL concept you were trying to illustrate. How did you _help_ the OP? The question that started this thread is an example of a common class of SQL problems and several solutions exist. Your solution neither answered his query nor was it explained to the point that made it comprehendable. Please, please try to be less confusing (especially when responding to newbies). Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Here we are Shawn, With empty tables : +++---+--+---+--+--- --+--+--+-+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+--+---+--+--- --+--+--+-+ | 1 | PRIMARY| a | ALL | NULL | NULL | NULL | NULL |0 | Using where | | 2 | DEPENDENT SUBQUERY | b | ALL | NULL | NULL | NULL | NULL |0 | Using where | +++---+--+---+--+--- --+--+--+-+ 2 rows in set (0.00 sec) mysql mysql explain select parentid,max(datestamp) from table2 - group by parentid; ++-++--+---+--+-+--- ---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++--+---+--+-+--- ---+--+-+ | 1 | SIMPLE | table2 | ALL | NULL | NULL |NULL | NULL |0 | Using temporary; Using filesort | ++-++--+---+--+-+--- ---+--+-+ One or two table scans ? Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 22:01 To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- 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]
Re: Query question
Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Hi, Im sorry to disappoint you but this is an anti-performance solution. Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table). Imagine that table2 has 30.000.000 records, and not good indexes. you can wait for your answer a long time. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 22:17 To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
RE: Query question
Peter, I'm unfamiliar with the @d := section you describe. Is this psudo code or real syntax? thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:17 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
Re: Query question
Title: Message Mathias, Im sorry todisappoint you but this is an anti-performance solution. Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table). This 2-query solution uses neither a join nor a subquery. What do you mean? PB - mathias fatene wrote: Hi, Im sorry todisappoint you but this is an anti-performance solution. Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table). Imagine that table2 has 30.000.000 records, and not good indexes. you can wait for your answer a long time. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: lundi 25 avril 2005 22:17 To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Jeff McKeon [EMAIL PROTECTED] wrote on 04/25/2005 04:08:29 PM: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 OK, then you need to collect your child-table maximums in one pass and build your actual query in the second (the non-subquery version of the example I sent). Let's find all of the child records where user not like 'john'. ( I will exclude all users whose name starts with 'john') CREATE TEMPORARY TABLE lastRecords SELECT parentID, max(datetime_field_name_here) as latest FROM table2 WHERE user NOT LIKE 'john%' GROUP BY parentID; You had to exclude 'john' at this stage because you want the latest child record that isn't 'john'. Make sense? Of course, you will need to adjust this to meet whatever conditions you really want. SELECT t1.*, t2.* FROM table1 t1 LEFT JOIN lastRecords r ON r.parentID = t1.id LEFT JOIN table2 t2 ON t2.parentID = r.parentID AND t2.datetime_field_name_here = r.latest; That will give you all of the records from table1 and only the most recent record from table2 (if it even exists). I used the LEFT JOIN (not an INNER JOIN) so that you can see all of the records from table1. If I had used INNER JOINs you would have only seen those records that matched up with the conditions you placed on table2. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Query question
Title: Message That's real syntax for inline assignment of a column value to a user variable. What MySQL version are you using? PB Jeff McKeon wrote: Peter, I'm unfamiliar with the "@d := " section you describe. Is this psudo code or real syntax? thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Monday, April 25, 2005 4:17 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
3.23. no control over this right now or i'd upgrade, believe me! jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:43 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question That's real syntax for inline assignment of a column value to a user variable. What MySQL version are you using? PB Jeff McKeon wrote: Peter, I'm unfamiliar with the @d := section you describe. Is this psudo code or real syntax? thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:17 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff
Re: Query question
Title: Message Jeff, 3.23. no control over this right now or i'd upgrade, believe me! Yep, I maintain websites with the same problem. Shawn Green just posted a solution that doesn't need inline user variable assignment. PB - Jeff McKeon wrote: 3.23. no control over this right now or i'd upgrade, believe me! jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Monday, April 25, 2005 4:43 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question That's real syntax for inline assignment of a column value to a user variable. What MySQL version are you using? PB Jeff McKeon wrote: Peter, I'm unfamiliar with the "@d := " section you describe. Is this psudo code or real syntax? thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Monday, April 25, 2005 4:17 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
mathias fatene [EMAIL PROTECTED] wrote on 04/25/2005 04:24:42 PM: Hi, Im sorry to disappoint you but this is an anti-performance solution. Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table). Imagine that table2 has 30.000.000 records, and not good indexes. you can wait for your answer a long time. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer snip Mathias, I do appreciate your energy and willingness to contribute to the list. I am not affiliated with MySQL or any of its subsidiaries and I have no special privileges to police what happens on this list. I am a fellow contributor just as you. With that said, I feel that I must seriously question your level of experience and ability to form useful responses. When you say and don't use joins if you can (all data in the mother table), It seems to me that you are proposing that in order to eliminate JOINs in queries that all data should be flattened into one single table. Not only is this incorrect advice but it undermines the many reasons for using a relational database system (RDBMS) in the first place. I would love to compare the performance of a properly normalized and indexed relational data structure against a single flat table for all but the most trivial of data sets. The nomalized data will not only take up less room on the disk but it will perform extremely well (especially for larger data sets). The single-table model you proposed will not scale to more than a few hundred thousand rows before the table's size becomes a bottleneck. Some queries will take a long time to finish against 30 million row tables, even with good indexes on them. Your extreme counter example was a non-starter. The original poster acknowledges that they are new (no offence intended) and I feel that your posts were hardly helpful at best and most likely counter-productive. Please, take the time to read your ansers from the perspective of the person you are responding to. Try to keep in mind not only their language skills (as this is a multi-national list) but their experience level and even sometimes their age (we have many students looking for help on here and some of them are still teenagers). Please be more accurate, thoughtful, and descriptive the next time you post, OK? With greatest humility, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Query question
thanks, I'll give that a try tomorrow. :o) Jeffrey S. McKeon Manager of Information Technology Telaurus Communications LLC [EMAIL PROTECTED] +1 (973) 889-8990 ex 209 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:36 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: RE: Query question Jeff McKeon [EMAIL PROTECTED] wrote on 04/25/2005 04:08:29 PM: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 OK, then you need to collect your child-table maximums in one pass and build your actual query in the second (the non-subquery version of the example I sent). Let's find all of the child records where user not like 'john'. ( I will exclude all users whose name starts with 'john') CREATE TEMPORARY TABLE lastRecords SELECT parentID, max(datetime_field_name_here) as latest FROM table2 WHERE user NOT LIKE 'john%' GROUP BY parentID; You had to exclude 'john' at this stage because you want the latest child record that isn't 'john'. Make sense? Of course, you will need to adjust this to meet whatever conditions you really want. SELECT t1.*, t2.* FROM table1 t1 LEFT JOIN lastRecords r ON r.parentID = t1.id LEFT JOIN table2 t2 ON t2.parentID = r.parentID AND t2.datetime_field_name_here = r.latest; That will give you all of the records from table1 and only the most recent record from table2 (if it even exists). I used the LEFT JOIN (not an INNER JOIN) so that you can see all of the records from table1. If I had used INNER JOINs you would have only seen those records that matched up with the conditions you placed on table2. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Query question
Hi, If my englsih is so bad, i'll try to explain and stop this thread now. I'm not teaching, i'm answering questions. If someone wants to read docs, he (she) doesn't ask a question on the list. So if i answer, i answer the question, just the question. You want to know my level of knowledgne, 10 years, oracle, sybase, sqlserver, db2. I can help for migration from or to... I said don't use joins for the query given in the example or queries using just the joining columns from the first table. Normal forms is bla bla here ... See also about covering indexes. That can help. This is the query given by Jeff : So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' The only clause is about table2.parentid = 100 or child.id = 100. that's the same. All the other clauses are on table2. This is the exampel given by Jeff. If you want absolutely LEFT outer joins for that (without other columns from table1), i say you good luck, this can (also) do the trick. That's all. - If you give me real examples, i can help you to give you to find the right (if i can) query plan. Tuning is my first target when i think a query. I never suggest nested loops, but relationnal algebra. I'm not supposed speeking to students but DBAs, for specific question. Sorry if i run up against your sensitivity, but we are not speaking about the same thing. And please if you have to criticize or complete an answer, it's your right. The list is for that. If you want to speak to me as your student, this is enough. I never did it when i was teacher 11 years ago. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 23:02 To: mathias fatene Cc: 'Jeff McKeon'; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Query question mathias fatene [EMAIL PROTECTED] wrote on 04/25/2005 04:24:42 PM: Hi, Im sorry to disappoint you but this is an anti-performance solution. Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table). Imagine that table2 has 30.000.000 records, and not good indexes. you can wait for your answer a long time. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer snip Mathias, I do appreciate your energy and willingness to contribute to the list. I am not affiliated with MySQL or any of its subsidiaries and I have no special privileges to police what happens on this list. I am a fellow contributor just as you. With that said, I feel that I must seriously question your level of experience and ability to form useful responses. When you say and don't use joins if you can (all data in the mother table), It seems to me that you are proposing that in order to eliminate JOINs in queries that all data should be flattened into one single table. Not only is this incorrect advice but it undermines the many reasons for using a relational database system (RDBMS) in the first place. I would love to compare the performance of a properly normalized and indexed relational data structure against a single flat table for all but the most trivial of data sets. The nomalized data will not only take up less room on the disk but it will perform extremely well (especially for larger data sets). The single-table model you proposed will not scale to more than a few hundred thousand rows before the table's size becomes a bottleneck. Some queries will take a long time to finish against 30 million row tables, even with good indexes on them. Your extreme counter example was a non-starter. The original poster acknowledges that they are new (no offence intended) and I feel that your posts were hardly helpful at best and most likely counter-productive. Please, take the time to read your ansers from the perspective of the person you are responding to. Try to keep in mind not only their language skills (as this is a multi-national list) but their experience level and even sometimes their age (we have many students looking for help on here and some of them are still teenagers). Please be more accurate, thoughtful, and descriptive the next time you post, OK? With greatest humility, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question
Three tables like this: -- product_lines -- id title -- manufacturer -- id title -- manufacturer_product_line_index -- id product_line_id manufacturer_id The index provides a one to many relationship - one product line can be associated with more than one company. How do I get a list of product lines not in the index? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
SELECT product_lines.* FROM product_lines LEFT JOIN manufacturer_product_line_index ON manufacturer_product_line_index.product_line_id = product_lines.id WHERE product_lines.id IS NULL -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 11:39 AM To: mysql@lists.mysql.com Subject: Query question Three tables like this: -- product_lines -- id title -- manufacturer -- id title -- manufacturer_product_line_index -- id product_line_id manufacturer_id The index provides a one to many relationship - one product line can be associated with more than one company. How do I get a list of product lines not in the index? Thanks, Ed -- 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]
Re: Query question
Ed Lazor [EMAIL PROTECTED] wrote on 04/07/2005 12:39:01 PM: Three tables like this: -- product_lines -- id title -- manufacturer -- id title -- manufacturer_product_line_index -- id product_line_id manufacturer_id The index provides a one to many relationship - one product line can be associated with more than one company. How do I get a list of product lines not in the index? Thanks, Ed To list all of the products without any entries in your index table: SELECT p.id, p.title FROM product_lines p LEFT JOIN manufacturer_product_line_index i on i.product_line_id = p.id WHERE i.id is null; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Query question
Whew, thanks Jon =) -Original Message- SELECT product_lines.* FROM product_lines LEFT JOIN manufacturer_product_line_index ON manufacturer_product_line_index.product_line_id = product_lines.id WHERE product_lines.id IS NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
Just turn your subquery into another join SELECT C2.City, N.Distance FROM Cities C INNER JOIN Nbc N ON C.CityID = N.PrimaryCityID INNER JOIN Cities C2 ON C2.cityID = N.CityID WHERE C.City = 'Los Angeles' AND N.Distance 20 Shawn Green Database Administrator Unimin Corporation - Spruce Pine Graham Anderson [EMAIL PROTECTED] wrote on 04/01/2005 04:49:31 PM: I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance 20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- 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]
Re: Newbie: MYSQL nested query question
I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance 20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- 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]
Re: Newbie: MYSQL nested query question
What was wrong with Graham's simpler query? PB - Graham Anderson wrote: I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance 20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
In the simple query... the city field showed the result 'Los Angeles' in every row the distance field showed incorrect results to :( City| Distance Los Angeles 18 Los Angeles 5 Los Angeles 7 ... On Apr 1, 2005, at 1:59 PM, Peter Brawley wrote: What was wrong with Graham's simpler query? PB - Graham Anderson wrote: I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance 20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- 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]
Re: Newbie: MYSQL nested query question
strangely, the query works intermittently :( SELECT ( SELECT City FROM Cities WHERE CityId = N.CityId ), N.Distance FROM Cities C JOIN Nbc N ON C.CityId = N.PrimaryCityId WHERE C.City = 'Los Angeles' AND N.Distance 20 sometimes it works...other times it gives the mysql query error: show keys from tbl_properties.php: Missing parameter: table huh ? On Apr 1, 2005, at 2:21 PM, Graham Anderson wrote: In the simple query... the city field showed the result 'Los Angeles' in every row the distance field showed incorrect results to :( City| Distance Los Angeles 18 Los Angeles 5 Los Angeles 7 ... On Apr 1, 2005, at 1:59 PM, Peter Brawley wrote: What was wrong with Graham's simpler query? PB - Graham Anderson wrote: I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance 20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: MYSQL nested query question
What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId = N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question
I want to get everything from user than if record exist in admin so
user has admin(administrator) in table user with user.id =
admin.admin_id, so I need to get 'admin' first_name and last_name
If there is no record in table admin with adin.user_id = user.id ,
than I need at least all records from user
Table: user
| id| int(10) | | PRI | NULL
| auto_increment |
| email | varchar(100)| YES | | NULL
||
| password | varchar(45) | YES | | NULL
||
| first_name| varchar(100)| YES | | NULL
||
| last_name | varchar(100)| YES | | NULL
||
| type | enum('admin','user')| YES | | NULL
| auto_increment |
Table: admin
| id| int(10) | | PRI | NULL| auto_increment |
| admin_id | int(10) | YES | | NULL||
| user_id | int(10) | YES | | NULL||
| date | datetime | YES | | NULL||
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
Jerry Swanson [EMAIL PROTECTED] wrote on 03/29/2005 11:43:56 AM:
I want to get everything from user than if record exist in admin so
user has admin(administrator) in table user with user.id =
admin.admin_id, so I need to get 'admin' first_name and last_name
If there is no record in table admin with adin.user_id = user.id ,
than I need at least all records from user
Table: user
| id| int(10) | | PRI | NULL
| auto_increment |
| email | varchar(100)| YES | | NULL
||
| password | varchar(45) | YES | | NULL
||
| first_name| varchar(100)| YES | | NULL
||
| last_name | varchar(100)| YES | | NULL
||
| type | enum('admin','user')| YES | | NULL
| auto_increment |
Table: admin
| id| int(10) | | PRI | NULL| auto_increment |
| admin_id | int(10) | YES | | NULL||
| user_id | int(10) | YES | | NULL||
| date | datetime | YES | | NULL||
I think I understand your need: for all users, list the user's name and
the name of their administrator, if an administrator exists
SELECT u.type
, u.first_name
, u.last_name
, au.first_name as admin_first
, au.last_name as admin_last
FROM user u
LEFT JOIN admin a
on a.user_id = u.id
LEFT JOIN user au
on au.id = a.admin_ID;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Sub Query question
Hi there, I was wondering if its possible to be able to send a field from the outer table to be used as the where statement for the sub query ? something like this select somefield from table 1, (select count(*) from table2 inner join table1 using somekey where table1.key=somefield) as alias where somefield is in the outer table what i was also hoping to achieve was something like this select somefield from table 1, (select field2 from table2 inner join table1 using somekey where table1.key=somefield) as alias and alias would contain rows of a one to many resulset :| that would be cool , so then i can list in a datagrid without having to do a nested loop ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question
Hi, all I hope somebody can help me. Situation Three tables 1. ++++ | id | name | region | ++++ | 13 | Name1 | 1 | | 15 | Name2 | 2 | | 47 | Name3 | 1 | | 57 | Name4 | 2 | | 65 | Name5 | 2 | | 77 | Name6 | 1 | | 85 | Name7 | 1 | ++++ 2. ++++--+ | id | Date | amount1| current | ++++--+ | 13 | 21.7.1967 | 9900 |N | | 13 | 21.7.1968 | 9800 |J | | 57 | 11.9.1999 | 12800 |J | | 65 | 24.6.1991 | 1200 |N | | 65 | 21.7.1967 | 1275 |J | | 85 | 14.2.2001 | 45000 |J | ++++--+ 3. ++---++ | id | Year | amount2| ++---++ | 13 | 1967 | 100 | | 13 | 1968 | 100 | | 13 | 1969 | 125 | | 15 | 1967 | 200 | | 15 | 1968 | 220 | | 15 | 1969 | 220 | | 47 | 1967 | 500 | | 47 | 1968 | 580 | | 47 | 1969 | 550 | ++---++ In table 1 there are all IDs. In tables 2 and 3 there can be some IDs. As you see, in region 1 there are the IDs 13,47,77 and 85. In table 2 only 13 and 85, in table 3 only 13 and 47. What I would like to have is a result like this: ++---+--+ | region | sum(amount1) if current=J | sum(amount2) | ++---+--+ | 1| 54800 | 1630 | | 2| 14075 | 640 | ++---+--+ Many thanks in advance Joachim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best Query Question
This has to be so simple, but my solution runs much slower than I would expect it to. I'm wondering if there is a more efficient way to do this type of query. I have a table of email messages, I have another table containing all of the email addresses linked to each email message I want to search for email messages in the email mesasge table that have both [EMAIL PROTECTED] and [EMAIL PROTECTED] as recipients in the address table select * from mailarchive, addresses as address1, addresses as address2 where ( mailarchive.MailArchiveRecordID=address1.emaillink and mailarchive.MailArchiveRecordID=address2.emaillink ) and address1.emailaddress=\[EMAIL PROTECTED] and address2.emailaddress=\[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculating User Ranks (SQL Query Question)
On 22-Dec-2004 Michael J. Pawlowsky wrote:
Im trying to come up with a more efficient method to do this.
I have a table where people enter some info into the table.
snip
I would like to allow the users to be able to see where they stand
rank
wise with everyone else.
Right now I basically do a SELECT count(1) as entries, user_id GROUP
BY
user_id ORDER BY entries DESC.
snip
I was wondering if anyone could think of a better way to do this.
Add a rank column that gets recalculated after each entry is added?
php code:
function calc_user_ranks($limit=100) {
global $dbconn;
$dbconn-Execute('SET @x:=0');
$qry = SELECT (@x:[EMAIL PROTECTED]) AS rank, user_id, count(1) AS
entries
FROM user
GROUP BY user_id ORDER BY entries DESC LIMIT $limit;
$dbconn-Execute($qry);
while($row = $r-FetchRow())
$rank[$row['user_id']] = $row['rank'];
$dbconn-Execute('UPDATE user SET rank=0');
foreach($rank as $id = $r) {
$qry = UPDATE user SET rank=$r WHERE user_id=$id;
$dbconn-Execute($qry);
}
}
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question
I use a table to log what pages on the website are getting visits with a table structure like this: ID DateAdded URL Now I'm trying to query the database to see which URLs are most popular, but I'm not sure how to go about doing this. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
This will return the top 50 urls in descending order of popularity. SELECT URL, count(1) as popularity FROM yourtablename GROUP BY URL ORDER BY popularity DESC LIMIT 50; Feel free to adjust as needed. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Lazor [EMAIL PROTECTED] wrote on 12/23/2004 01:45:30 PM: I use a table to log what pages on the website are getting visits with a table structure like this: ID DateAdded URL Now I'm trying to query the database to see which URLs are most popular, but I'm not sure how to go about doing this. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Ed, Try the following query select ID, DateAdded, URL, count(*) as 'cnt' from mytable group by URL order by cnt desc It should display the most numerous URLs in the table. dimitar -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Thursday, December 23, 2004 1:46 PM To: 'mysql' Subject: Query question I use a table to log what pages on the website are getting visits with a table structure like this: ID DateAdded URL Now I'm trying to query the database to see which URLs are most popular, but I'm not sure how to go about doing this. Any ideas? Thanks, Ed -- 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]
RE: Query question
Thanks, Shawn. I didn't think count would just limit to the items being grouped - very handy =) -Ed SELECT URL, count(1) as popularity FROM yourtablename GROUP BY URL ORDER BY popularity DESC LIMIT 50; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calculating User Ranks (SQL Query Question)
Im trying to come up with a more efficient method to do this. I have a table where people enter some info into the table. The more entries they add the more points they get. (1 point per entry). I would like to allow the users to be able to see where they stand rank wise with everyone else. Right now I basically do a SELECT count(1) as entries, user_id GROUP BY user_id ORDER BY entries DESC. Then loop through the results until I match their user_id and count how many times I go through the loop and that is how I can give them their ranking. It just seems like a big waste to loop through the results until I find their user_id. I was wondering if anyone could think of a better way to do this. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculating User Ranks (SQL Query Question)
Try this: CREATE TEMPORARY TABLE tmpRankings ( Rank int auto_increment, entries int, user_id int ) INSERT tmpRankings (points, user_id) SELECT count(1), user_id FROM sometablenamehere GROUP BY user_id ORDER BY entries DESC; This way the tmpRankings table contains an ordered, numbered list of all of your user_id's (in proper ranking order). If you change tmpRankings from a Temporary table to a permanent table you could rebuild it each time someone makes a new entry. That would save you the overhead of recomputing the entire table each time someone just wants to know their ranking but the data hasn't changed because nobody has made any entries since the last check. There are other tweaks you can do to save recomputes, too, but this is the general idea. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 12/22/2004 04:00:45 PM: Im trying to come up with a more efficient method to do this. I have a table where people enter some info into the table. The more entries they add the more points they get. (1 point per entry). I would like to allow the users to be able to see where they stand rank wise with everyone else. Right now I basically do a SELECT count(1) as entries, user_id GROUP BY user_id ORDER BY entries DESC. Then loop through the results until I match their user_id and count how many times I go through the loop and that is how I can give them their ranking. It just seems like a big waste to loop through the results until I find their user_id. I was wondering if anyone could think of a better way to do this. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General query question
Hi, Mysql 4.0.14 In a seconrio, some reocrds are missing from a child table. If we run this query it returns the missing records: select a.field1, b.field2 from table1 a left join table2 b on (a.field1 = b.field1) where b.field1 is null I want to create entries in the child table (table2) for the missing records. In table2 the primary key is of type Integer, for each new entry it should be Max(table2.PrimaryKeyfield) + 1. How can I do this in one sql command (or is it possilbe at all)? something like: Insert into table2 (primarykeyfiled, field1, field2) Max(table2.priamrykeyfield) + 1 select a.field1, a.field2 from table1 a left join table2 b on (a.field1 = b.field1) where b.field1 is null regards ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General query question
I want to create entries in the child table (table2) for the missing records. In table2 the primary key is of type Integer, for each new entry it should be Max(table2.PrimaryKeyfield) + 1. Why not make the primary key in table2 autoincrement? If you have an autoincrement field as primary key MySQL will do the max(..)+1 automatically for you! A query like Insert into table2 (field1, field2) select a.field1, a.field2 from table1 a left join table2 b on (a.field1 = b.field1) where b.field1 is null could then be used to fill up the missing records in table2... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General query question
Thanks, I did think of it but not having the option as this is linked to executables, which I'm sure have some sorts of calculation for this field to calculate the next value. regards --- Jigal van Hemert [EMAIL PROTECTED] wrote: I want to create entries in the child table (table2) for the missing records. In table2 the primary key is of type Integer, for each new entry it should be Max(table2.PrimaryKeyfield) + 1. Why not make the primary key in table2 autoincrement? If you have an autoincrement field as primary key MySQL will do the max(..)+1 automatically for you! A query like Insert into table2 (field1, field2) select a.field1, a.field2 from table1 a left join table2 b on (a.field1 = b.field1) where b.field1 is null could then be used to fill up the missing records in table2... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question
I have a query statement set up for record returns based on various where statements. The select statement consists of a number of joins. One of those joins includes a field that is marked no null. Recently I did a mass insertion into the table. Into this particular no null field were place 0's (zeroes). Now the queries are not running correctly. I'm wondering if it's possible that these 0's could be effecting the query ? Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
Quite possibly since 0 could also mean false depending on your comparison operator. For instance, using a generic if statement, these two would both evaluate to false: if(0) if(null) You should be very specific when checking for NULL. WHERE field IS NOT NULL or WHERE field IS NULL Also, you may want to look into the NULL safe comparison operator: http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html On Nov 16, 2004, at 7:17 AM, Stuart Felenstein wrote: I have a query statement set up for record returns based on various where statements. The select statement consists of a number of joins. One of those joins includes a field that is marked no null. Recently I did a mass insertion into the table. Into this particular no null field were place 0's (zeroes). Now the queries are not running correctly. I'm wondering if it's possible that these 0's could be effecting the query ? Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re:[SOLVED] Query question
--- Brent Baisley [EMAIL PROTECTED] wrote: Quite possibly since 0 could also mean false depending on your comparison operator. For instance, using a generic if statement, these two would both evaluate to false: if(0) if(null) You should be very specific when checking for NULL. WHERE field IS NOT NULL or WHERE field IS NULL Also, you may want to look into the NULL safe comparison operator: http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html Thank you Brent. I discovered it was the 0's. Thank you for the information. I'll be doing my reading. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple query question
I have a table in which the first column is either 1 or 0. The second column is a number between 0 and 59. I need to perform a query that returns entries where: 1. IF the first column is 1, the second column is NOT 0 2. IF the first column is 0, the second column is anything. It seems simple, but I'm not getting it right. Any ideas? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple query question
* John Mistler I have a table in which the first column is either 1 or 0. The second column is a number between 0 and 59. I need to perform a query that returns entries where: 1. IF the first column is 1, the second column is NOT 0 2. IF the first column is 0, the second column is anything. It seems simple, but I'm not getting it right. Any ideas? Try this: SELECT * FROM tab1 WHERE (col1 = 1 AND col2 0) OR (col1 = 0) When combining AND and OR, proper use of parantheses is important. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Question
I'm hoping I can present this correctly. I'm trying to determine how to set up my where condition as, 1 way has already failed me. While I continue to figure this out (i'm a noob), I hope asking for some advice here won't be too awful. There is one main table where data is inserted and that I'm querying against, but this main table is comprised of ID's from other static tables. VendorJobs is the main table, here is the select and from's: -- SELECT `VendorJobs`.`JobID`, `VendorJobs`.`Entered`, `VendorSignUp`.`CompanyName`, `StaIndTypes`.`CareerCategories`, `StaUSCities`.`City`, `USStates`.`States`, `VendorJobs`.`AreaCode`, `staTaxTerm`.`TaxTerm`, `VendorJobs`.`PayRate`, `staTravelReq`.`TravelReq`, `VendorJobDetails`.`JobTitle`, `VendorJobDetails`.`Details`, `VendorJobs`.`PostStart` FROM `VendorJobs` INNER JOIN `VendorSignUp` ON (`VendorJobs`.`VendorID` = `VendorSignUp`.`VendorID`) INNER JOIN `StaIndTypes` ON (`VendorJobs`.`Industry` = `StaIndTypes`.`CareerIDs`) LEFT OUTER JOIN `StaUSCities` ON (`VendorJobs`.`LocationCity` = `StaUSCities`.`CityID`) LEFT OUTER JOIN `USStates` ON (`VendorJobs`.`LocationState` = `USStates`.`StateID`) LEFT OUTER JOIN `staTaxTerm` ON (`VendorJobs`.`TaxTerm` = `staTaxTerm`.`TaxTermID`) INNER JOIN `staTravelReq` ON (`VendorJobs`.`TravelReq` = `staTravelReq`.`TravelReqID`) INNER JOIN `VendorJobDetails` ON (`VendorJobs`.`JobID` = `VendorJobDetails`.`JobID`) -- The where condition is going to have multiple AND's (I've considered UNION but don't think they are appropriate here) First I should say that run as a complete dump, it returns all the records correctly , with all the id's translated into the correct lable. i.e State, CA is stored in VendorJobs as CA, but in the return (and this isn't the greatest example) it's California. So now I want to add the where's but doing: where `VendorJobs`.`CareerCategories` = Finance is returning an error. I think because VendorJobs only knows Finance by Fin. I think what it should be : StaIndTypes`.`CareerCategories` = Finance I could be wrong, about to try it. What's confusing me is the join, and how the join maintains the integrity of the record. If that makes sense, cause what I just said, doesn't to me. Yet, that's the only way I could say it for now. Alrighty, flame away Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
I think I'm on the right track but still in question After all the joins I added a and LocationState = x. I'm not totally sure, because I want to search for records based on (for now)3 conditions (state, city, industry). Two things I should mention , the somewhat strange notation is becaue I'm using one of dem fancy visual query editors. Secondly, I'm using this query in a web page that receives the codes via url / variables. So, and I hope I don't get slammed for talking web dev here. But the problem is someone may choose one field and not another , so I want a return on whether they chose 1 or all 3 variables. Hope that makes sense. i.e. They choose the state, but leave the city and industy blank. The way it's set up now with and ..., and ..., and it will only return a record if I put in all 3. If I do an OR, then a second on any additonal OR's would get bypassed if I understand correctly. Sorry, I might just be thinking outloud. Please do not boot. Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm hoping I can present this correctly. I'm trying to determine how to set up my where condition as, 1 way has already failed me. While I continue to figure this out (i'm a noob), I hope asking for some advice here won't be too awful. There is one main table where data is inserted and that I'm querying against, but this main table is comprised of ID's from other static tables. VendorJobs is the main table, here is the select and from's: -- SELECT `VendorJobs`.`JobID`, `VendorJobs`.`Entered`, `VendorSignUp`.`CompanyName`, `StaIndTypes`.`CareerCategories`, `StaUSCities`.`City`, `USStates`.`States`, `VendorJobs`.`AreaCode`, `staTaxTerm`.`TaxTerm`, `VendorJobs`.`PayRate`, `staTravelReq`.`TravelReq`, `VendorJobDetails`.`JobTitle`, `VendorJobDetails`.`Details`, `VendorJobs`.`PostStart` FROM `VendorJobs` INNER JOIN `VendorSignUp` ON (`VendorJobs`.`VendorID` = `VendorSignUp`.`VendorID`) INNER JOIN `StaIndTypes` ON (`VendorJobs`.`Industry` = `StaIndTypes`.`CareerIDs`) LEFT OUTER JOIN `StaUSCities` ON (`VendorJobs`.`LocationCity` = `StaUSCities`.`CityID`) LEFT OUTER JOIN `USStates` ON (`VendorJobs`.`LocationState` = `USStates`.`StateID`) LEFT OUTER JOIN `staTaxTerm` ON (`VendorJobs`.`TaxTerm` = `staTaxTerm`.`TaxTermID`) INNER JOIN `staTravelReq` ON (`VendorJobs`.`TravelReq` = `staTravelReq`.`TravelReqID`) INNER JOIN `VendorJobDetails` ON (`VendorJobs`.`JobID` = `VendorJobDetails`.`JobID`) -- The where condition is going to have multiple AND's (I've considered UNION but don't think they are appropriate here) First I should say that run as a complete dump, it returns all the records correctly , with all the id's translated into the correct lable. i.e State, CA is stored in VendorJobs as CA, but in the return (and this isn't the greatest example) it's California. So now I want to add the where's but doing: where `VendorJobs`.`CareerCategories` = Finance is returning an error. I think because VendorJobs only knows Finance by Fin. I think what it should be : StaIndTypes`.`CareerCategories` = Finance I could be wrong, about to try it. What's confusing me is the join, and how the join maintains the integrity of the record. If that makes sense, cause what I just said, doesn't to me. Yet, that's the only way I could say it for now. Alrighty, flame away Stuart -- 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]
Re: Query Question
Stuart Felenstein wrote: I'm hoping I can present this correctly. I'm trying to determine how to set up my where condition as, 1 way has already failed me. While I continue to figure this out (i'm a noob), I hope asking for some advice here won't be too awful. There is one main table where data is inserted and that I'm querying against, but this main table is comprised of ID's from other static tables. VendorJobs is the main table, here is the select and from's: query reformatted so I could read it -- SELECT VJ.JobID, VJ.Entered, VSU.CompanyName, StaIndTypes.CareerCategories, StaUSCities.City, USStates.States, VJ.AreaCode, staTaxTerm.TaxTerm, VJ.PayRate, staTravelReq.TravelReq, VendorJobDetails.JobTitle, VendorJobDetails.Details, VJ.PostStart FROM VendorJobs VJ INNER JOIN VendorSignUp VSU ON VJ.VendorID = VSU.VendorID INNER JOIN StaIndTypes ON VJ.Industry = StaIndTypes.CareerIDs LEFT JOIN StaUSCities ON VJ.LocationCity = StaUSCities.CityID LEFT JOIN USStates ON VJ.LocationState = USStates.StateID LEFT JOIN staTaxTerm ON VJ.TaxTerm = staTaxTerm.TaxTermID INNER JOIN staTravelReq ON VJ.TravelReq = staTravelReq.TravelReqID INNER JOIN VendorJobDetails ON VJ.JobID = VendorJobDetails.JobID -- The where condition is going to have multiple ANDs (I've considered UNION but don't think they are appropriate here) AND and UNION are opposites. ANDs narrow your results, because only rows which match all AND conditions are selected. UNION, like OR, increases your result set, because rows only have to match any one of the conditions. That is, SELECT * FROM atable WHERE a = 1 OR b = 2; is equivalent to SELECT * FROM atable WHERE a = 1 UNION SELECT * FROM atable WHERE b = 2; See the manual for details http://dev.mysql.com/doc/mysql/en/UNION.html. First I should say that run as a complete dump, it returns all the records correctly , with all the id's translated into the correct lable. i.e State, CA is stored in VendorJobs as CA, but in the return (and this isn't the greatest example) it's California. So now I want to add the wheres but doing: where `VendorJobs`.`CareerCategories` = Finance is returning an error. I think because VendorJobs only knows Finance by Fin. `VendorJobs`.`CareerCategories` = Finance gives you an *error* (as opposed to no match), because you have no column named Finance. You (mysql) can tell Finance is the name of a column because it has no quotes. Of course, you meant to compare VendorJobs.CareerCategories to the constant string 'Finance', so you should use WHERE `VendorJobs`.`CareerCategories` = 'Finance' ... But, your condition should be WHERE column_name = 'a value in that column' ... So, if the string 'Fin' is what is actually stored in the CareerCategories column, you need WHERE `VendorJobs`.`CareerCategories` = 'Fin' ... I think what it should be : StaIndTypes`.`CareerCategories` = Finance I could be wrong, about to try it. OK, now I'm confused. Which column of which table contains the string 'Finance' or 'Fin'? Oh, is 'Fin' an ID for the row in StaIndTypes where the full string 'Finance' resides? If that's the case, since you are joining to StaIndTypes, either match (VendorJobs.CareerCategories` = 'Fin' or StaIndTypes = 'Finance') should work. What's confusing me is the join, and how the join maintains the integrity of the record. If that makes sense, cause what I just said, doesn't to me. Yet, that's the only way I could say it for now. Can you elaborate? I could answer the question I *think* you're asking, but that might be a waste. What about joins is confusing you? Alrighty, flame away No flames. I originally learned mysql by reading the manual, reading the list, working on a mysql/php/apache project, and asking questions. There's nothing wrong with that. The trick is not to let your development race too far ahead of your understanding, so as to minimize the rewriting you have to do whenever you say, Aha! Now I get it. Stuart Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Well I feel like maybe I wasted some bandwidth here. I think what I'm looking for is a square peg in a round hole. That won't work. More to the point :) , I do not having a problem with the AND / OR / IN / NOT / etc. What I think I was attempting was to come up with a SQL statement that will work with an unknown factor. I'm now thinking that this isn't the path to take. Here is where I'm at, and as this goes into web dev, I am trying to figure out if I have any choices strictly using SQL. I have 3 choices (3 seperate fields to query) a user can submit, but none are strictly required. Chances are only 1 will be used. If I do a where x = 1 or y = 2 or z = 3 then regardless of the y or z, x is coming back. It's not even looking at y or z. All AND requires all three conditions are met. Lastly I was interested in NOT, since by default there is an assigned value to the non used form field. Yet, all of my where conditions are = resset1... which is the variable that gets passed over. Not sure how to say where LocationState NOT XXX and leave the resset in place. Sorry if this is all confusing. I am taking my time and wouldn't think of just throwing something up unless it works in the 999,999,999 ways it should. Thank you, Stuart --- Michael Stassen [EMAIL PROTECTED] wrote: Stuart Felenstein wrote: I'm hoping I can present this correctly. I'm trying to determine how to set up my where condition as, 1 way has already failed me. While I continue to figure this out (i'm a noob), I hope asking for some advice here won't be too awful. There is one main table where data is inserted and that I'm querying against, but this main table is comprised of ID's from other static tables. VendorJobs is the main table, here is the select and from's: query reformatted so I could read it -- SELECT VJ.JobID, VJ.Entered, VSU.CompanyName, StaIndTypes.CareerCategories, StaUSCities.City, USStates.States, VJ.AreaCode, staTaxTerm.TaxTerm, VJ.PayRate, staTravelReq.TravelReq, VendorJobDetails.JobTitle, VendorJobDetails.Details, VJ.PostStart FROM VendorJobs VJ INNER JOIN VendorSignUp VSU ON VJ.VendorID = VSU.VendorID INNER JOIN StaIndTypes ON VJ.Industry = StaIndTypes.CareerIDs LEFT JOIN StaUSCities ON VJ.LocationCity = StaUSCities.CityID LEFT JOIN USStates ON VJ.LocationState = USStates.StateID LEFT JOIN staTaxTerm ON VJ.TaxTerm = staTaxTerm.TaxTermID INNER JOIN staTravelReq ON VJ.TravelReq = staTravelReq.TravelReqID INNER JOIN VendorJobDetails ON VJ.JobID = VendorJobDetails.JobID -- The where condition is going to have multiple ANDs (I've considered UNION but don't think they are appropriate here) AND and UNION are opposites. ANDs narrow your results, because only rows which match all AND conditions are selected. UNION, like OR, increases your result set, because rows only have to match any one of the conditions. That is, SELECT * FROM atable WHERE a = 1 OR b = 2; is equivalent to SELECT * FROM atable WHERE a = 1 UNION SELECT * FROM atable WHERE b = 2; See the manual for details http://dev.mysql.com/doc/mysql/en/UNION.html. First I should say that run as a complete dump, it returns all the records correctly , with all the id's translated into the correct lable. i.e State, CA is stored in VendorJobs as CA, but in the return (and this isn't the greatest example) it's California. So now I want to add the wheres but doing: where `VendorJobs`.`CareerCategories` = Finance is returning an error. I think because VendorJobs only knows Finance by Fin. `VendorJobs`.`CareerCategories` = Finance gives you an *error* (as opposed to no match), because you have no column named Finance. You (mysql) can tell Finance is the name of a column because it has no quotes. Of course, you meant to compare VendorJobs.CareerCategories to the constant string 'Finance', so you should use WHERE `VendorJobs`.`CareerCategories` = 'Finance' ... But, your condition should be WHERE column_name = 'a value in that column' ... So, if the string 'Fin' is what is actually stored in the CareerCategories column, you need WHERE `VendorJobs`.`CareerCategories` = 'Fin' ... I think what it should be : StaIndTypes`.`CareerCategories` = Finance I could be wrong, about to try it. OK, now I'm confused. Which column of which table contains the string 'Finance' or 'Fin'? Oh, is 'Fin' an ID for the row in StaIndTypes where the full string 'Finance' resides? If that's the case, since you are joining to StaIndTypes, either match (VendorJobs.CareerCategories` = 'Fin' or StaIndTypes = 'Finance') should work. What's confusing me is the join, and how the join
SQL Query Question
Im not sure if this is possible or not. I have a Sales leads table. Part of the table has 2 employee_ids. 1. The Sales person the lead is assigned to. 2. The Marketing person that generated the lead. Then there is a employee table that has ids and names. When generating a report for leads I would like to lookup the name of the employee. I know I can do it with a seperate query, but I'm wondering if I can also do it in one query. Something like: SELECT employee.name as sales_name, employee.name as marketing_name, leads.id FROM leads, employee WHERE employee.id = leads.salesid AND employee.id = leads.marketingid Is there someway this can be done? Thanks for any assistance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael Michael J. Pawlowsky wrote: Im not sure if this is possible or not. I have a Sales leads table. Part of the table has 2 employee_ids. 1. The Sales person the lead is assigned to. 2. The Marketing person that generated the lead. Then there is a employee table that has ids and names. When generating a report for leads I would like to lookup the name of the employee. I know I can do it with a seperate query, but I'm wondering if I can also do it in one query. Something like: SELECT employee.name as sales_name, employee.name as marketing_name, leads.id FROM leads, employee WHERE employee.id = leads.salesid AND employee.id = leads.marketingid Is there someway this can be done? Thanks for any assistance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Thanks a lot Michael. A regular join did not seem to work. But when I tried a LEFT JOIN it worked. A cut down example of it is the following. SELECT global_lead.id, rep_no, es.fname as sales_name, em.fname as marketing_name FROM global_lead LEFT JOIN global_employee es ON global_lead.rep_no = es.id LEFT JOIN global_employee em ON global_lead.entered_by = em.id WHERE global_lead.rep_no = 8 Michael Stassen wrote: You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Right. If the employee ID in either the rep_no or entered_by columns does not have a corresponding row in the global_employee table, then the regular join won't match that row. In that case, as you found, you need a LEFT JOIN, which guarantees you get the rows from the table on the left, and auto-creates NULL fields for the table on the right when it has no matching row. For reference, this is mentioned in the manual http://dev.mysql.com/doc/mysql/en/JOIN.html. Michael Michael J. Pawlowsky wrote: Thanks a lot Michael. A regular join did not seem to work. But when I tried a LEFT JOIN it worked. A cut down example of it is the following. SELECT global_lead.id, rep_no, es.fname as sales_name, em.fname as marketing_name FROM global_lead LEFT JOIN global_employee es ON global_lead.rep_no = es.id LEFT JOIN global_employee em ON global_lead.entered_by = em.id WHERE global_lead.rep_no = 8 Michael Stassen wrote: You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update query question
Have you tried this other way of making an inner join? UPDATE products_categories AS pc INNER JOIN products AS p ON pc.prod_id = p.id SET pc.prod_sequential_id = p.id But that does not seem right our you could say: UPDATE products_categories AS pc SET pc.prod_sequential_id = pc.prod_id and have the same statement. I think this is what you meant to say: UPDATE products_categories AS pc INNER JOIN products AS p ON pc.prod_id = p.id SET pc.prod_sequential_id = p.sequential_id Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | Chris W. Parker| | | [EMAIL PROTECTED]| | | .com| | || | | 07/06/2004 01:14 | | | PM | | || |-+ | | | | To: [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: update query question | | hello, i've had to change some of the tables in my db to accomodate some greater flexibility in the application that uses it and because of this i need to go through and update all the records. i've done one table by hand and it had about 100 records and took about 20 minutes. but this next table has about 550 records and i really don't feel like doing this all by hand. i'm using MySQL Control Center to do this editing so i'd like to know if there's a single SQL statement i could use to update all the rows. here is a simple representation: products: (pay no attention to the poor choice in column names. this is a retrofitting and will be fixed in later versions.) +--+---+ | id | sequential_id | +--+---+ | PRDX-41 | 1 | | ABCX-01 | 2 | | FF00-11 | 3 | \/\/\/\/\/\/\/\/ | ETC0-99 | 500 | +--+---+ the 'prod_sequential_id' column was added later to the products_categories table. products_categories: +-+-+++ | id | prod_id | prod_sequential_id | cat_id | +-+-+++ | 1 | PRDX-41 | 0 | 41 | | 2 | PRDX-41 | 0 | 15 | | 3 | ABCX-01 | 0 | 13 | | 4 | FF00-11 | 0 | 89 | \/\/\/\/\/\/\/\/ | 610 | ETC0-99 | 0 | 41 | +-+-+++ so... as you can see, prod_sequential_id has all 0's in its column. it should contain the value of products.sequential_id WHERE products_categories.prod_id = products.id. the problem is that i'm not sure how to do this all in one statement (or if it's even possible): (i know the following does not work, but it's basically the logic i think i need.) UPDATE products_categories AS pc, products AS p SET pc.prod_sequential_id = p.id WHERE pc.prod_id = p.id; thanks for your help. chris. -- 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]
RE: update query question
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] on Wednesday, July 07, 2004 11:08 AM said: Have you tried this other way of making an inner join? no i did not because i did know you could do a JOIN on an UPDATE. thanks for your suggestions i will try them out. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update query question
hello, i've had to change some of the tables in my db to accomodate some greater flexibility in the application that uses it and because of this i need to go through and update all the records. i've done one table by hand and it had about 100 records and took about 20 minutes. but this next table has about 550 records and i really don't feel like doing this all by hand. i'm using MySQL Control Center to do this editing so i'd like to know if there's a single SQL statement i could use to update all the rows. here is a simple representation: products: (pay no attention to the poor choice in column names. this is a retrofitting and will be fixed in later versions.) +--+---+ | id | sequential_id | +--+---+ | PRDX-41 | 1 | | ABCX-01 | 2 | | FF00-11 | 3 | \/\/\/\/\/\/\/\/ | ETC0-99 | 500 | +--+---+ the 'prod_sequential_id' column was added later to the products_categories table. products_categories: +-+-+++ | id | prod_id | prod_sequential_id | cat_id | +-+-+++ | 1 | PRDX-41 | 0 | 41 | | 2 | PRDX-41 | 0 | 15 | | 3 | ABCX-01 | 0 | 13 | | 4 | FF00-11 | 0 | 89 | \/\/\/\/\/\/\/\/ | 610 | ETC0-99 | 0 | 41 | +-+-+++ so... as you can see, prod_sequential_id has all 0's in its column. it should contain the value of products.sequential_id WHERE products_categories.prod_id = products.id. the problem is that i'm not sure how to do this all in one statement (or if it's even possible): (i know the following does not work, but it's basically the logic i think i need.) UPDATE products_categories AS pc, products AS p SET pc.prod_sequential_id = p.id WHERE pc.prod_id = p.id; thanks for your help. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Select count(distinct(field)) from table where field = 0 ? -Original Message- From: Laercio Xisto Braga Cavalcanti [mailto:[EMAIL PROTECTED] Sent: Monday, May 24, 2004 11:18 PM To: 'John Nichel'; 'MySQL List' Subject: RE: Query question You can do: Select count(distinct(field)) from table where field 0 Laercio. -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: segunda-feira, 24 de maio de 2004 14:37 To: MySQL List Subject: Query question Hi, I have a table which I want to select data from (obiviously). In this table, I have a field which is an integer, and defaults to 0. What I would like to do is count all rows in that table which not only equals 0 for the field, but has a distinct value which is greater than 0. id field 1 0 2 0 3 7 4 8 5 7 6 0 7 6 8 7 9 8 For the above example, my count should return 6. Three zero's count as 3, three seven's count as 1, two eight's count as 1, and one six counts as 1. I've tried... SELECT COUNT(*) FROM db.table WHERE ( field = 0 || ( field 0 DISTINCT field ) ) But it still returns the count of all the rows. -- John C. Nichel KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question
Hi, I have a table which I want to select data from (obiviously). In this table, I have a field which is an integer, and defaults to 0. What I would like to do is count all rows in that table which not only equals 0 for the field, but has a distinct value which is greater than 0. id field 1 0 2 0 3 7 4 8 5 7 6 0 7 6 8 7 9 8 For the above example, my count should return 6. Three zero's count as 3, three seven's count as 1, two eight's count as 1, and one six counts as 1. I've tried... SELECT COUNT(*) FROM db.table WHERE ( field = 0 || ( field 0 DISTINCT field ) ) But it still returns the count of all the rows. -- John C. Nichel KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
At 12:36 PM 5/24/2004, you wrote: Hi, I have a table which I want to select data from (obiviously). In this table, I have a field which is an integer, and defaults to 0. What I would like to do is count all rows in that table which not only equals 0 for the field, but has a distinct value which is greater than 0. id field 1 0 2 0 3 7 4 8 5 7 6 0 7 6 8 7 9 8 For the above example, my count should return 6. Three zero's count as 3, three seven's count as 1, two eight's count as 1, and one six counts as 1. I've tried... SELECT COUNT(*) FROM db.table WHERE ( field = 0 || ( field 0 DISTINCT field ) ) But it still returns the count of all the rows. John, You are trying to put 2 select statements into one. It is much easier if you try: select @numzero := count(*) from table1 where field=0; select @numdistinct := count(distinct field) from table1 where field 0 ; select @numzero + @numdistinct;; Mike -- John C. Nichel KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- 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]
Re: Query question
Rich Allen wrote: iH this should work test select * from xt; ++---+ | id | field | ++---+ | 1 | 0 | | 2 | 0 | | 3 | 7 | | 4 | 8 | | 5 | 7 | | 6 | 0 | | 7 | 6 | | 8 | 7 | | 9 | 8 | ++---+ 9 rows in set (0.00 sec) test select count(distinct(field)) + (select count(*) from xt where field=0) - 1 from xt; +--+ | count(distinct(field)) + (select count(*) from xt where field=0) - 1 | +--+ |6 | +--+ 1 row in set (0.01 sec) note that i subtract one since i counted a 0 value in the distinct part ... - hcir That's what I needed. Thanks! -- John C. Nichel KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
You can do: Select count(distinct(field)) from table where field 0 Laercio. -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: segunda-feira, 24 de maio de 2004 14:37 To: MySQL List Subject: Query question Hi, I have a table which I want to select data from (obiviously). In this table, I have a field which is an integer, and defaults to 0. What I would like to do is count all rows in that table which not only equals 0 for the field, but has a distinct value which is greater than 0. id field 1 0 2 0 3 7 4 8 5 7 6 0 7 6 8 7 9 8 For the above example, my count should return 6. Three zero's count as 3, three seven's count as 1, two eight's count as 1, and one six counts as 1. I've tried... SELECT COUNT(*) FROM db.table WHERE ( field = 0 || ( field 0 DISTINCT field ) ) But it still returns the count of all the rows. -- John C. Nichel KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- 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]
Re: Query question
On Mon, 2004-05-24 at 11:32, John Nichel wrote: Rich Allen wrote: iH this should work test select * from xt; ++---+ | id | field | ++---+ | 1 | 0 | | 2 | 0 | | 3 | 7 | | 4 | 8 | | 5 | 7 | | 6 | 0 | | 7 | 6 | | 8 | 7 | | 9 | 8 | ++---+ 9 rows in set (0.00 sec) test select count(distinct(field)) + (select count(*) from xt where field=0) - 1 from xt; +--+ | count(distinct(field)) + (select count(*) from xt where field=0) - 1 | +--+ |6 | +--+ 1 row in set (0.01 sec) note that i subtract one since i counted a 0 value in the distinct part ... - hcir That's what I needed. Thanks! Note that this solution will be off by one if there aren't any zeros in your data. Try this: select count(IF(field0,NULL,1)) + count(distinct IF(field0,field,NULL)) from test; -- . Garth Webb . [EMAIL PROTECTED] . . shoes * * schoenen * * chaussures * zapatos . Schuhe * * pattini * * sapatas * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
Garth, good catch! - hcir mysql - hcir On May 24, 2004, at 1:05 PM, Garth Webb wrote: On Mon, 2004-05-24 at 11:32, John Nichel wrote: Rich Allen wrote: iH this should work test select * from xt; ++---+ | id | field | ++---+ | 1 | 0 | | 2 | 0 | | 3 | 7 | | 4 | 8 | | 5 | 7 | | 6 | 0 | | 7 | 6 | | 8 | 7 | | 9 | 8 | ++---+ 9 rows in set (0.00 sec) test select count(distinct(field)) + (select count(*) from xt where field=0) - 1 from xt; + --+ | count(distinct(field)) + (select count(*) from xt where field=0) - 1 | + --+ | 6 | + --+ 1 row in set (0.01 sec) note that i subtract one since i counted a 0 value in the distinct part ... - hcir That's what I needed. Thanks! Note that this solution will be off by one if there aren't any zeros in your data. Try this: select count(IF(field0,NULL,1)) + count(distinct IF(field0,field,NULL)) from test; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
iH this should work test select * from xt; ++---+ | id | field | ++---+ | 1 | 0 | | 2 | 0 | | 3 | 7 | | 4 | 8 | | 5 | 7 | | 6 | 0 | | 7 | 6 | | 8 | 7 | | 9 | 8 | ++---+ 9 rows in set (0.00 sec) test select count(distinct(field)) + (select count(*) from xt where field=0) - 1 from xt; +--+ | count(distinct(field)) + (select count(*) from xt where field=0) - 1 | +--+ |6 | +--+ 1 row in set (0.01 sec) note that i subtract one since i counted a 0 value in the distinct part ... - hcir mysql - hcir On May 24, 2004, at 9:36 AM, John Nichel wrote: Hi, I have a table which I want to select data from (obiviously). In this table, I have a field which is an integer, and defaults to 0. What I would like to do is count all rows in that table which not only equals 0 for the field, but has a distinct value which is greater than 0. id field 1 0 2 0 3 7 4 8 5 7 6 0 7 6 8 7 9 8 For the above example, my count should return 6. Three zero's count as 3, three seven's count as 1, two eight's count as 1, and one six counts as 1. I've tried... SELECT COUNT(*) FROM db.table WHERE ( field = 0 || ( field 0 DISTINCT field ) ) But it still returns the count of all the rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
John Try select field, count(*) from db.table group by field; David -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, May 24, 2004 10:37 AM To: MySQL List Subject: Query question Hi, I have a table which I want to select data from (obiviously). In this table, I have a field which is an integer, and defaults to 0. What I would like to do is count all rows in that table which not only equals 0 for the field, but has a distinct value which is greater than 0. idfield 1 0 2 0 3 7 4 8 5 7 6 0 7 6 8 7 9 8 For the above example, my count should return 6. Three zero's count as 3, three seven's count as 1, two eight's count as 1, and one six counts as 1. I've tried... SELECT COUNT(*) FROM db.table WHERE ( field = 0 || ( field 0 DISTINCT field ) ) But it still returns the count of all the rows. -- John C. Nichel KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- 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]
Query question
I'm trying to select specified data from a field in a table.
The field from which the data has to come contains the following:
'something;else;anything;everything;name;my' (and so on), it's a long text.
I need in the case just 'my' from the field, thus between the ';'. This
time there are only two characters, but I can't say by forehand how many
letters there will be.
The only thing that's sure it comes after 'name', so I have the
following query:
SELECT SYS_IDX, LEFT(RIGHT(C,
LENGTH(C)-LOCATE(';',C,LOCATE('name',C))),10) FROM A;
This return's up to ten characters after the name, somethimes this is to
much, sometimes to many. Does anybody knows how to go from here.
I'm using mysql 3.21
AC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
On 21-Apr-2004 Alex croes wrote:
I'm trying to select specified data from a field in a table.
The field from which the data has to come contains the following:
'something;else;anything;everything;name;my' (and so on), it's a long
text.
I need in the case just 'my' from the field, thus between the ';'.
This
time there are only two characters, but I can't say by forehand how
many
letters there will be.
The only thing that's sure it comes after 'name', so I have the
following query:
SELECT SYS_IDX, LEFT(RIGHT(C,
LENGTH(C)-LOCATE(';',C,LOCATE('name',C))),10) FROM A;
This return's up to ten characters after the name, somethimes this is
to
much, sometimes to many. Does anybody knows how to go from here.
LEFT(SUBSTRING_INDEX(foo, ';', 1), 10)
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Query Question - Need help of Gurus.
Ben Dinnerville wrote:
You have a redundant clause in your query - the distinct is not needed when
you are doing a group by on the same field ('Call Svr Tag ID') - not sure
how the optimiser in MySQL will handle this. In some RDBMS's the duplicate
clause will be optimised down to 1 clause, so you wont cop a performance
hit, but just because you think an optimiser will do something is no reason
not to write the query right the first time - always makes it easier on the
optimiser and write the query with as few possible predicates and clauses as
possible.
DISTINCT is not a function you feed a column into. It is a SELECT keyword
which prevents duplicate rows. For example,
CREATE TABLE t (v1 int, v2 int);
INSERT INTO t VALUES (1, 1), (1, 2);
SELECT DISTINCT(v1), v2 FROM t;
+--+--+
| v1 | v2 |
+--+--+
|1 |1 |
|1 |2 |
+--+--+
2 rows in set (0.00 sec)
Once you add GROUP BY, you are guaranteed unique rows, one for each group,
so DISTINCT adds nothing.
What indexes do you have on the table?
You need an index on the 'Journal Create Date' (PS identifiers with spaces
in the name are bad practice!).
An index on the Call_Svc_Tag_ID column may also assist with the group by
function - you need to run an explain to see what indexes are needed.
The indexes were listed at the bottom of the original post. He already has
indexes on `Journal Create Date` and `Call Svc Tag ID`, but MySQL will only
use one index per table, so separate indexes won't help much. He should
definitely run an EXPLAIN. I expect EXPLAIN will list both as possible
keys, and which, if any, it picked. What is needed, I expect, is a
multi-column index on those 2 columns:
ALTER TABLE 31909_859552
ADD INDEX Tag_Created (`Call Svc Tag ID`, `Journal Create Date`);
Then try again:
SELECT `Call Svc Tag ID`,
Count(*) as counter,
`Journal Create Date`
FROM 31909_859552
WHERE `Journal Create Date` between '2004-03-13' AND '2004-03-16'
AND `Call Svc Tag ID`'null'
GROUP BY `Call Svc Tag ID`
HAVING counter 3
ORDER BY counter;
Note that sorting by the count can't use an index, so it will be slower than
if you had ordered by `Call Svc Tag ID`.
Also, not sure how the optimiser and indexing in MySQL handles the between
predicate, but you might want to try doing a greater than equal to test and
a less than test in the place of the between test - eg
WHERE Journal_Create_Date = '2004-03-13' AND Journal_Create_Date
'2004-03-16'
MySQL handles BETWEEN just fine. If you think about it, you explicitly set
the range with BETWEEN, but the optimizer has to put the two inequalities
joined with AND together to get the same range. In other words, BETWEEN is
easier.
In answer to one of the other follow ups, a count() should not result in a
full table scan when there are other predicates in the query - they will be
resolved first and then the count() will be done over the temporary result
set.
Cheers,
Ben
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Hi All,
I have a huge Database which contains about 500,000 records, (will be
adding about 20-25k records every week)
I need to run group queries and output the same to a web interface.
snip
- At present a select takes anywhere from 20 seconds to 10 minutes on
such queries.
MySQL/MyISAM is designed to give fast SELECTS, so 10 minutes seems excessive.
What can I do?
Till now we were using MS Access, and no web based interface.
Running a normal MySQL 4.0 install on windows 2000, 256 MB RAM, 2.6 GHz
P4 NON-HT
About 10 users at a time would be connecting initially.
I'm no Win2000 expert, but that doesn't sound like enough RAM, especially if
the web server is on the same machine. Ideally, you'd like your data cached
in RAM, but this one table appears to be 100 Mb or so, not even counting the
indexes. With a query taking 10 minutes, I wonder if you're running out of
memory and thrashing the disk.
I know theres a lot of tweaking possible with MySQL.
Please advise, because I would need a lot of such queries, fetching
through JSP, and displaying them in a presentable form.
Table Structure is as Follows:
Journal Created By varchar(255) No
Case Substatus ID varchar(255) Yes NULL
Case Status ID varchar(255) Yes NULL
Call Rep Employee Name varchar(255) Yes NULL
Call Rep Employee ID varchar(10) Yes NULL
Call Rep DPS Login ID varchar(10) Yes NULL
Call Rep Profile ID varchar(15) Yes NULL
Call Rep Section Code varchar(15) Yes NULL
Call Rep Country Code char(3) Yes NULL
Journal Create Date date No -00-00
Journal Create Time time Yes NULL
Call Svc Tag ID varchar(255) Yes NULL
Order Num of Svc Tag varchar(255) Yes NULL
Indexes:
RE: Slow Query Question - Need help of Gurus.
The indexes were listed at the bottom of the original post. Woops, didnt see that far down, should have scrolled a little further :) What is needed, I expect, is a multi-column index on those 2 columns: ALTER TABLE 31909_859552 ADD INDEX Tag_Created (`Call Svc Tag ID`, `Journal Create Date`); Definatly get an index across all queried fields, especially in this case where there are only 2 columns in the result set, you may be able to avoid hitting the data leaf's of the table all together and retrieve all info from the index alone, saving you the added IO on the data leaf's Then try again: SELECT `Call Svc Tag ID`, Count(*) as counter, `Journal Create Date` FROM 31909_859552 WHERE `Journal Create Date` between '2004-03-13' AND '2004-03-16' AND `Call Svc Tag ID`'null' GROUP BY `Call Svc Tag ID` HAVING counter 3 ORDER BY counter; The count(*) will be causing some havoc here, as all columns in the underlying table(s) will have to be read in, negating any direct hits to the index's and causing a lot more IO than is needed. Change it to a count on one of the columns in the result set or simply a count(1) as counter - will give you the same result without any IO. Note that sorting by the count can't use an index, so it will be slower than if you had ordered by `Call Svc Tag ID`. This is something that will need testing. Ordering on a varchar(255) column (call svc tag ID) is going to be a lot more inefficient that ordering on a numeric column, especially when the ordering is happening on the reduced result set of the group by and predicate clauses, however the indexing available on the real column may negate any deficit. MySQL handles BETWEEN just fine. If you think about it, you explicitly set the range with BETWEEN, but the optimizer has to put the two inequalities joined with AND together to get the same range. In other words, BETWEEN is easier. I am sure that MySQL does handle the between just fine, but this is not to say that it will handle the between better that a gt / lt clause in this particular scenario. I know the between sounds like it might be easier, but internally the between is likely to be translated into a gt / lt clause by the optimiser anyhow as the only way to check if something is between 2 values is the check if it is greater than the min and less than the max. Just give the 2 different queries a go and see if there is any time difference. Also noticed that the table seems to be a fairly flat structure (hard to tell defiantly from looking at one sample). By this I mean that there are a lot of varchar(255)'s in the table, and looking at the naming of the columns, these seem to be id type fields. You might be able to gain some performance by normalising the database a little by moving some of the large varchar fields out to a related table and referencing by a numeric type id, this could greatly reduce the amount of data in your base table, especially considering the number of rows you are talking about storing and could give you an IO based performance increase. Once again, might need to test and play around a little with different models here. What kind of proportion of null values are you expecting for the Call Svc Tag ID column? Depending on this, you may or may not be better off restructuring the query to filter out the null values post grouping - eg as an extra for the having clause having counter 3 and call svc tag id null) - My mind is not super clear on this one at the moment, but am not sure if / how null values get indexed in MySQL (any feedback on this one anyone?) another one that maybe only testing will show if it helps or not. Also, are you looking for null values, or the word null in the column? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Query Question - Need help of Gurus.
Ben, - Original Message - From: Ben Dinnerville Sent: Monday, April 19, 2004 1:49 AM Subject: RE: Slow Query Question - Need help of Gurus. snip Then try again: SELECT `Call Svc Tag ID`, Count(*) as counter, `Journal Create Date` FROM 31909_859552 WHERE `Journal Create Date` between '2004-03-13' AND '2004-03-16' AND `Call Svc Tag ID`'null' GROUP BY `Call Svc Tag ID` HAVING counter 3 ORDER BY counter; The count(*) will be causing some havoc here, as all columns in the underlying table(s) will have to be read in, negating any direct hits to the index's and causing a lot more IO than is needed. Change it to a count on one of the columns in the result set or simply a count(1) as counter - will give you the same result without any IO. COUNT(*) is not a problem. It won't cause the data file to be read if just the index can be used. EXPLAIN will show the same plan for COUNT(*) and COUNT(1). :-) Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Query Question - Need help of Gurus.
Ben Dinnerville wrote: snip Note that sorting by the count can't use an index, so it will be slower than if you had ordered by `Call Svc Tag ID`. This is something that will need testing. Ordering on a varchar(255) column (call svc tag ID) is going to be a lot more inefficient than ordering on a numeric column, especially when the ordering is happening on the reduced result set of the group by and predicate clauses, however the indexing available on the real column may negate any deficit. The ordering happens on the reduced result set either way, so that isn't relevant here. The key point is the index. Without any index, it is certainly true that a varchar(255) should take longer to sort than an int, but in this case the varchar column is already sorted in the index, while the int is the result of a calculation. No additional work is needed to sort the varchar, while the int must be sorted. On the other hand, sorting ints is usually fast, so I don't think this should be a big factor. MySQL handles BETWEEN just fine. If you think about it, you explicitly set the range with BETWEEN, but the optimizer has to put the two inequalities joined with AND together to get the same range. In other words, BETWEEN is easier. I am sure that MySQL does handle the between just fine, but this is not to say that it will handle the between better that a gt / lt clause in this particular scenario. I know the between sounds like it might be easier, but internally the between is likely to be translated into a gt / lt clause by the optimiser anyhow as the only way to check if something is between 2 values is the check if it is greater than the min and less than the max. Just give the 2 different queries a go and see if there is any time difference. I won't pretend to be an expert on the internals of the optimizer, but I think you are missing the value of the index. Another way to tell if a value is between two others is to check its relative position in the index. Both the between and the 2 inequalities define a range. With the index, the optimizer need merely find the start and end of the range and then grab everything from the one to the other. I am certainly in favor of testing, but I'd be surprised if you saw a measurable difference between the two. Also noticed that the table seems to be a fairly flat structure (hard to tell definitely from looking at one sample). By this I mean that there are a lot of varchar(255)'s in the table, and looking at the naming of the columns, these seem to be id type fields. You might be able to gain some performance by normalising the database a little by moving some of the large varchar fields out to a related table and referencing by a numeric type id, this could greatly reduce the amount of data in your base table, especially considering the number of rows you are talking about storing and could give you an IO based performance increase. Once again, might need to test and play around a little with different models here. Excellent point. Normalizing would help a lot. Unless there are 500,000 Call Reps, there's a LOT of wasted space in this table. To make matters worse, the rows are variable length, so there's a penalty for wasted space. At the least, the Call Rep info should be in its own table, and the 6 Call Rep columns should be replaced with an int column containing the Call Rep key. Similarly, the Case Status and Substatus should be moved to their own table(s?). If at all possible, all tables should be fixed length (CHARs) of a reasonable size (will you ever really have a 255 char Call Rep Employee Name?) rather than variable length (VARCHARs). Usually, I'd say that disk is cheap, so go ahead and waste some space to save on time, but in this case, I expect we'll save so much space from normalizing that even changing VARCHAR to CHAR we'll still end up ahead spacewise. Dropping any unused indexes could also help, in general. For example, unless we need to select based on time of day without regard to date, the index on `Journal Create Time` is unlikely to be used. In that case drop both date and time indexes and replace them with one multicolumn index. snip Also, are you looking for null values, or the word null in the column? I noticed that too and forgot to mention it. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow Query Question - Need help of Gurus.
You have a redundant clause in your query - the distinct is not needed when
you are doing a group by on the same field ('Call Svr Tag ID') - not sure
how the optimiser in MySQL will handle this. In some RDBMS's the duplicate
clause will be optimised down to 1 clause, so you wont cop a performance
hit, but just because you think an optimiser will do something is no reason
not to write the query right the first time - always makes it easier on the
optimiser and write the query with as few possible predicates and clauses as
possible.
What indexes do you have on the table?
You need an index on the 'Journal Create Date' (PS identifiers with spaces
in the name are bad practice!).
An index on the Call_Cvc_Tag_ID column may also assist with the group by
function - you need to run an explain to see what indexes are needed.
Also, not sure how the optimiser and indexing in MySQL handles the between
predicate, but you might want to try doing a greater than equal to test and
a less than test in the place of the between test - eg
WHERE Journal_Create_Date = '2004-03-13' AND Journal_Create_Date
'2004-03-16'
In answer to one of the other follow ups, a count() should not result in a
full table scan when there are other predicates in the query - they will be
resolved first and then the count() will be done over the temporary result
set.
Cheers,
Ben
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Sunday, 18 April 2004 06:50
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Slow Query Question - Need help of Gurus.
Importance: High
Hi All,
I have a huge Database which contains about 500,000 records, (will be
adding about 20-25k records every week)
I need to run group queries and output the same to a web interface.
An example is:
SELECT DISTINCT(`Call Svc Tag ID`),Count(`Call Svc Tag ID`) as counter,
`Journal Create Date` FROM 31909_859552 WHERE `Journal Create Date`
between '2004-03-13' AND '2004-03-16' and `Call Svc Tag ID`'null'
GROUP BY `Call Svc Tag ID` having count(`Call Svc Tag ID`)3 order by
counter;
--basically finding out repeat journal entries over a period of 3 days
for the same tag..
(the no. of days may vary.. 2/3/4/5... , and same goes with the having
count clause..)
The output results to about 150-1 rows or so.. Based on selections
-I've designed the table to be a MyISAM (I don't care how long inserts
take, that will be a once-a-week-one-time process on the server, I want
the selects to be fast)
- At present a select takes anywhere from 20 seconds to 10 minutes on
such queries.
What can I do?
Till now we were using MS Access, and no web based interface.
Running a normal MySQL 4.0 install on windows 2000, 256 MB RAM, 2.6 GHz
P4 NON-HT
About 10 users at a time would be connecting initially.
I know theres a lot of tweaking possible with MySQL.
Please advise, because I would need a lot of such queries, fetching
through JSP, and displaying them in a presentable form.
Table Structure is as Follows:
Journal Created By varchar(255) No
Case Substatus ID varchar(255) Yes NULL
Case Status ID varchar(255) Yes NULL
Call Rep Employee Name varchar(255) Yes NULL
Call Rep Employee ID varchar(10) Yes NULL
Call Rep DPS Login ID varchar(10) Yes NULL
Call Rep Profile ID varchar(15) Yes NULL
Call Rep Section Code varchar(15) Yes NULL
Call Rep Country Code char(3) Yes NULL
Journal Create Date date No -00-00
Journal Create Time time Yes NULL
Call Svc Tag ID varchar(255) Yes NULL
Order Num of Svc Tag varchar(255) Yes NULL
Indexes:
Keyname Type Cardinality Action Field
Journal Created By INDEX None Journal Created By
Call Rep Employee ID INDEX None Call Rep Employee ID
Call Rep DPS Login ID INDEX None Call Rep DPS Login ID
Call Rep Profile ID INDEX None Call Rep Profile ID
Call Rep Country Code INDEX None Call Rep Country Code
Journal Create Time INDEX None Journal Create Time
Journal Create Date INDEX None Journal Create Date
Call Svc Tag ID INDEX None Call Svc Tag ID
Regards,
Amit Wadhwa,
Data Analyst
--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
very simple query question
Hello i have a simple query select u.*,p.* from users u, profiles p where u.uname = p.uname and u.level != 0 Is there any tricks to make this use an index. If i do level=0 is uses an index , but != does not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
The you will need to use the second format. DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 4:09 PM Subject: Re: SQL Query Question - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:57 Subject: RE: SQL Query Question If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. -- 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]
Re: very simple query question
i have a simple query select u.*,p.* from users u, profiles p where u.uname = p.uname and u.level != 0 Is there any tricks to make this use an index. If i do level=0 is uses an index , but != does not. MySQL only uses an index if it will return less than approx. 30% of the records. It tries to guess this by looking at the cardinality of the index (the estimated number of different entries). In your case level=0 returned less than 30% of the records, so obviously level!=0 will return more than 70% of the records and MySQL desides to do a table scan (which is probably more efficient than first looking in the index and then searching for almost every single record). You can do a SELECT u.*,p.* FROM users AS u FORCE INDEX (u_level_index) JOIN profiles AS p USING (uname) WHERE u.level != 0 but this will only slow the query down. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
If you do any math on your column, no index on the column can be used. If possible, you should always try to write your condition so that the calculations are done on the value(s) to compare to, not on the column. So, assuming you have no rows with future timestamps, something like this should work: SELECT * FROM yourtable WHERE queue_time BETWEEN CURDATE() AND NOW(); If you might have timestamps later than NOW for some reason, you could change this to something like: SELECT * FROM yourtable WHERE queue_time BETWEEN CURDATE() AND (CURDATE + INTERVAL 1 DAY - INTERVAL 1 SECOND); To select yesterday's rows, you could do something like: SELECT * FROM yourtable WHERE queue_time BETWEEN (CURDATE() - INTERVAL 1 DAY) AND (CURDATE() - INTERVAL 1 SECOND); To help you visualize what's happening here, try SELECT CURDATE(), NOW(), CURDATE() + INTERVAL 1 DAY - INTERVAL 1 SECOND, CURDATE() - INTERVAL 1 DAY, CURDATE() - INTERVAL 1 SECOND; You say you've been wrestling with the docs, so you probably already seen these, but just in case: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html Michael Victor Pendleton wrote: The you will need to use the second format. DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() -Original Message- From: Dirk Bremer (NISC) - Original Message - From: Victor Pendleton [EMAIL PROTECTED] If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow Query Question - Need help of Gurus.
Hi All, I have a huge Database which contains about 500,000 records, (will be adding about 20-25k records every week) I need to run group queries and output the same to a web interface. An example is: SELECT DISTINCT(`Call Svc Tag ID`),Count(`Call Svc Tag ID`) as counter, `Journal Create Date` FROM 31909_859552 WHERE `Journal Create Date` between '2004-03-13' AND '2004-03-16' and `Call Svc Tag ID`'null' GROUP BY `Call Svc Tag ID` having count(`Call Svc Tag ID`)3 order by counter; --basically finding out repeat journal entries over a period of 3 days for the same tag.. (the no. of days may vary.. 2/3/4/5... , and same goes with the having count clause..) The output results to about 150-1 rows or so.. Based on selections -I've designed the table to be a MyISAM (I don't care how long inserts take, that will be a once-a-week-one-time process on the server, I want the selects to be fast) - At present a select takes anywhere from 20 seconds to 10 minutes on such queries. What can I do? Till now we were using MS Access, and no web based interface. Running a normal MySQL 4.0 install on windows 2000, 256 MB RAM, 2.6 GHz P4 NON-HT About 10 users at a time would be connecting initially. I know theres a lot of tweaking possible with MySQL. Please advise, because I would need a lot of such queries, fetching through JSP, and displaying them in a presentable form. Table Structure is as Follows: Journal Created By varchar(255) No Case Substatus ID varchar(255) Yes NULL Case Status ID varchar(255) Yes NULL Call Rep Employee Name varchar(255) Yes NULL Call Rep Employee ID varchar(10) Yes NULL Call Rep DPS Login ID varchar(10) Yes NULL Call Rep Profile ID varchar(15) Yes NULL Call Rep Section Code varchar(15) Yes NULL Call Rep Country Code char(3) Yes NULL Journal Create Date date No -00-00 Journal Create Time time Yes NULL Call Svc Tag ID varchar(255) Yes NULL Order Num of Svc Tag varchar(255) Yes NULL Indexes: Keyname Type Cardinality Action Field Journal Created By INDEX None Journal Created By Call Rep Employee ID INDEX None Call Rep Employee ID Call Rep DPS Login ID INDEX None Call Rep DPS Login ID Call Rep Profile ID INDEX None Call Rep Profile ID Call Rep Country Code INDEX None Call Rep Country Code Journal Create Time INDEX None Journal Create Time Journal Create Date INDEX None Journal Create Date Call Svc Tag ID INDEX None Call Svc Tag ID Regards, Amit Wadhwa, Data Analyst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Query Question
I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
This works for Oracle, give it a try, use any format you want for the MM/DD/YY area. select to_char(queue_time, 'MM/DD/YY'); Scott Purcell -Original Message- From: Dirk Bremer (NISC) [mailto:[EMAIL PROTECTED] Sent: Friday, April 16, 2004 2:55 PM To: [EMAIL PROTECTED] Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- 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]
RE: SQL Query Question
WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- 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]
Re: SQL Query Question
- Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:06 Subject: RE: SQL Query Question WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc Victor, I just want to match the date, not the time, i.e. all of the rows for the current date regardless of the time they were entered. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 3:25 PM Subject: Re: SQL Query Question - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:06 Subject: RE: SQL Query Question WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc Victor, I just want to match the date, not the time, i.e. all of the rows for the current date regardless of the time they were entered. Thanks! -- 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]
Re: SQL Query Question
- Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:57 Subject: RE: SQL Query Question If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
On Fri, 2004-04-16 at 14:09, Dirk Bremer (NISC) wrote: - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:57 Subject: RE: SQL Query Question If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. You could keep any index you have and do it this way: SELECT a,b FROM x WHERE queue_time BETWEEN date_format(curdate(), %Y%m%e00) AND date_format(curdate(), %Y%m%e235959); -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Query Question
Hi, I am wondering if this is possible: Say I have a table with 2 columns, Column_Count and Column_TotalCount; Is it possible to use a query to select all rows from the table where Column_Count is greater than Column_TotalCount? Like this: SELECT * FROM mytable WHERE Column_Count Column_TotalCount; Thanks.
Re: Query Question
Hello, Friday, April 16, 2004, 12:50:21 AM, you wrote: M Is it possible to use a query to select all rows from the table M where Column_Count is greater than Column_TotalCount? M Like this: M SELECT * FROM mytable WHERE Column_Count Column_TotalCount; Perhaps you ought to try it before posting? ;) -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
I did try it, and it doesn't work, I was looking for Ideas that will work. Hello, Friday, April 16, 2004, 12:50:21 AM, you wrote: M Is it possible to use a query to select all rows from the table M where Column_Count is greater than Column_TotalCount? M Like this: M SELECT * FROM mytable WHERE Column_Count Column_TotalCount; Perhaps you ought to try it before posting? ;) -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- 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]
Re[2]: Query Question
Hello, Friday, April 16, 2004, 12:56:32 AM, you wrote: M I did try it, and it doesn't work, I was looking for Ideas that will work. Obviously not, because that's exactly how you do it. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
At 16:56 -0700 4/15/04, MYSQL wrote: I did try it, and it doesn't work, I was looking for Ideas that will work. Looks to me like it should work. Try this and see what you get: SELECT Column_Count, Column_TotalCount, Column_Count ColumnTotalCount FROM mytable; That'll show you what's in the columns, as well as the result of the comparison. Might be instructive. Hello, Friday, April 16, 2004, 12:50:21 AM, you wrote: M Is it possible to use a query to select all rows from the table M where Column_Count is greater than Column_TotalCount? M Like this: M SELECT * FROM mytable WHERE Column_Count Column_TotalCount; Perhaps you ought to try it before posting? ;) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Question
It should work if both columns are numerical. Say int, decimal, float, double and so on. Babs -Ursprüngliche Nachricht- Von: MYSQL [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 16. April 2004 01:50 An: mysql Betreff: Query Question Hi, I am wondering if this is possible: Say I have a table with 2 columns, Column_Count and Column_TotalCount; Is it possible to use a query to select all rows from the table where Column_Count is greater than Column_TotalCount? Like this: SELECT * FROM mytable WHERE Column_Count Column_TotalCount; Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question...
Manuele wrote: This might sound silly to many... so sorry in advance... in mysql4 Suppose I have 2 tables: tableA has 3 columns, 2 of them reference the same column of tableB Example: TableA (Items) Id - FirstType - SecondType 0 - 1 - NULL 1 - 2 - 3 TableB (Types) Id - Description 1 - DummyType 2 - AnotherDummyType 3 - MoreDummies Is there a way to get 'Description' field for all records in tableA related to FirstType AND SecondType ? I have tried a union query, but with no luck... Also tried any possible (to me) variant of JOINS... no luck either The problem is that I am only able to get TableB.Description once, possibly for the first match... What I would need is a resoult of this type: TableA.Id - TableB.Description (of TableA.FirstType) - TableB.Description (of TableA.SecondType) 0- DummyType- NULL 1- AnotherDummyType - MoreDummies Hope I made myself clear as possible... Any help appreciated Thanks, Manuele P.S. This would be easy to make if one could make a synonym for TableB or a 'virtual' copy of it ... maybe a view? But so far mysql hasn't views... so I need to bypass the problem at application level, wich isn't really that good for me... Any use of multiple queries would also be not good... I need to make this in a single query ... if possible You can make a virtual copy of a table with aliases. Something like this: SELECT A.Id, B1.Description, B2.Description FROM TableA A, TableB B1, TableB B2 WHERE A.FirstType = B1.Id AND A.SecondType = B2.Id ORDER BY A.Id; As you can see, there is one copy of TableB aliased as B1 and another copy of TableB aliased as B2. Unfortunately, this won't quite work with your sample data. You get: +--+--+-+ | Id | Description | Description | +--+--+-+ |1 | AnotherDummyType | MoreDummies | +--+--+-+ There is no result where TableA.Id = 0 because of the NULL in SecondType. One solution would be to alter your data slightly. Something like: UPDATE TableA SET SecondType=0 WHERE SecondType IS NULL; INSERT INTO TableB (Id, Description) VALUES (0,NULL); That is, instead of storing a NULL key in the SecondType column of TableA, store a key which points to a row of TableB with a NULL Description. Then, the same query I gave above yields +--+--+-+ | Id | Description | Description | +--+--+-+ |0 | DummyType| NULL| |1 | AnotherDummyType | MoreDummies | +--+--+-+ which is the result you requested. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question using REGEXP
You could also do this with REGEXP, using [[::]] and [[::]] which are
character classes that match word boundaries, including comma, beginning
of line, and end of line. Then finding rows which include 2, for
example, would look something like this:
SELECT * FROM yourtable WHERE column REGEXP [[::]]2[[::]];
This may be faster than the CONCAT/LIKE version, as it compares the
actual column value rather than a function of the column value.
That said, it might be a good idea to consider alternative ways to store
your data. As it stands, it appears you are trying to store multiple
(numeric) values in a single (char) column, which usually isn't a good
idea.
If you have a fixed set of numbers which show up in your numbers list
column, and if there are 64 or fewer of them, you may wish to consider
the SET type http://www.mysql.com/doc/en/SET.html.
Another option is to store one number value per row. Your table would
then look something like
Record Column
1 12
2 1
2 2
2 5
2 6
3 1
3 12
3 24
3 45
4 2
4 6
Then finding records with a value of 2, for example, becomes simply
SELECT * FROM yourtable WHERE column = 2;
This is likely to be the fastest, as this query could take advantage of
an index on column.
Michael
Matt W wrote:
Hi Anthony,
You don't need REGEXP for this; LIKE will do. Try something like this:
... WHERE CONCAT(',', Column, ',') LIKE '%,2,%'
to search for rows that contain 2.
Hope that helps.
Matt
- Original Message -
From: award
Sent: Saturday, March 13, 2004 2:16 PM
Subject: query question using REGEXP
Hi,
I'm storing in a database numbers separated by comma if more than one
number i.e
Record # Column
112
21,2,5,6
31,12,24,45
4 2,6
Now if I want to search for records that has number 1 it will find
records 1,2,3
but what I want to return only record 2,3
an example is that if I'm looking for a record that has a number 2
it will print record 1,2,3,4 but what I want is only record 2,4
Any help is appreciated,
anthony
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Great QUERY question
JR Bullington [EMAIL PROTECTED] wrote: This is for those who love a challenge. I am trying to come up with a query that would calculate the Standard Deviation and Variance for 15 fields. Although in theory this is easily done in Access, MySQL does not have the same mathematical calculations that Access/SQL does. Here is the query as it stands in Access: Select avg(Item01) as Item01a, stdev(Item01) as Item01d, avg(Item02) as Item02a, stdev(Item02) as Item02d, avg(Item03) as Item03a, stdev(Item03) as Item03d, avg(Item04) as Item04a, stdev(Item04) as Item04d, avg(Item05) as Item05a, stdev(Item05) as Item05d, avg(Item06) as Item06a, stdev(Item06) as Item06d, avg(Item07) as Item07a, stdev(Item07) as Item07d, avg(Item08) as Item08a, stdev(Item08) as Item08d, avg(Item09) as Item09a, stdev(Item09) as Item09d, avg(Item10) as Item10a, stdev(Item10) as Item10d, avg(Item11) as Item11a, stdev(Item11) as Item11d, avg(Item12) as Item12a, stdev(Item12) as Item12d, avg(Item13) as Item13a, stdev(Item13) as Item13d, avg(Item14) as Item14a, stdev(Item14) as Item14d, avg(Item15) as Item15a, stdev(Item15) as Item15d, avg(overallscore) as overa, stdev(overallscore) as overd from tblFacultyEvalSurgery This is for an online grading system that, except for 5 pages, has been converted over to MySQL and Linux / Apache. 2 of which have this problem. Avg() is easy, but it's the StDev that I can't get. St Dev is made from Variance (or the mean), which again is not a function of MySQL. To view the Variance and StDev formulae, http://davidmlane.com/hyperstat/A16252.html. If you have any ideas, I will be working on this for the next few days. There are STD()/STDDEV() functions in the MySQL: http://www.mysql.com/doc/en/GROUP-BY-Functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Great QUERY question
This is for those who love a challenge. I am trying to come up with a query that would calculate the Standard Deviation and Variance for 15 fields. Although in theory this is easily done in Access, MySQL does not have the same mathematical calculations that Access/SQL does. Here is the query as it stands in Access: Select avg(Item01) as Item01a, stdev(Item01) as Item01d, avg(Item02) as Item02a, stdev(Item02) as Item02d, avg(Item03) as Item03a, stdev(Item03) as Item03d, avg(Item04) as Item04a, stdev(Item04) as Item04d, avg(Item05) as Item05a, stdev(Item05) as Item05d, avg(Item06) as Item06a, stdev(Item06) as Item06d, avg(Item07) as Item07a, stdev(Item07) as Item07d, avg(Item08) as Item08a, stdev(Item08) as Item08d, avg(Item09) as Item09a, stdev(Item09) as Item09d, avg(Item10) as Item10a, stdev(Item10) as Item10d, avg(Item11) as Item11a, stdev(Item11) as Item11d, avg(Item12) as Item12a, stdev(Item12) as Item12d, avg(Item13) as Item13a, stdev(Item13) as Item13d, avg(Item14) as Item14a, stdev(Item14) as Item14d, avg(Item15) as Item15a, stdev(Item15) as Item15d, avg(overallscore) as overa, stdev(overallscore) as overd from tblFacultyEvalSurgery This is for an online grading system that, except for 5 pages, has been converted over to MySQL and Linux / Apache. 2 of which have this problem. Avg() is easy, but it's the StDev that I can't get. St Dev is made from Variance (or the mean), which again is not a function of MySQL. To view the Variance and StDev formulae, http://davidmlane.com/hyperstat/A16252.html. If you have any ideas, I will be working on this for the next few days. Thanks! J.R.
query question...
This might sound silly to many... so sorry in advance... in mysql4 Suppose I have 2 tables: tableA has 3 columns, 2 of them reference the same column of tableB Example: TableA (Items) Id - FirstType - SecondType 0 - 1 - NULL 1 - 2 - 3 TableB (Types) Id - Description 1 - DummyType 2 - AnotherDummyType 3 - MoreDummies Is there a way to get 'Description' field for all records in tableA related to FirstType AND SecondType ? I have tried a union query, but with no luck... Also tried any possible (to me) variant of JOINS... no luck either The problem is that I am only able to get TableB.Description once, possibly for the first match... What I would need is a resoult of this type: TableA.Id - TableB.Description (of TableA.FirstType) - TableB.Description (of TableA.SecondType) 0- DummyType- NULL 1- AnotherDummyType- MoreDummies Hope I made myself clear as possible... Any help appreciated Thanks, Manuele P.S. This would be easy to make if one could make a synonym for TableB or a 'virtual' copy of it ... maybe a view? But so far mysql hasn't views... so I need to bypass the problem at application level, wich isn't really that good for me... Any use of multiple queries would also be not good... I need to make this in a single query ... if possible -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
