Need help with a sql query
Hello all: I have two tables: 1. MenuAccess: accessId fkMenuId fkGroupid View Execute 2. MenuOptions MenuId MenuName I would like to get all of the records in MenuOptions and any record in MenuAccess with a specific fkGroupid. For example: A. MenuAccess AccessId fkMenuID fkgroupid view execute 1 1 2 1 1 2 2 2 1 0 3 3 2 0 1 4 1 1 1 1 B. MenuOptions Menuid MenuName 1 Order Maintenance 2 Customer Maintenance 3 Reports IIf I run a query for fkgroupid = 1 I should get AccessId fkMenuID fkgroupid view execute MenuId MenuName 1 1 2 1 1 1 Order Maintenence Null Null Null Null Null 2 Customer Maintenence Null Null Null Null Null 3 Reports Any help will be appreciated. Thanks
Re: Need help with a sql query
Imran wrote: Hello all: ... I would like to get all of the records in MenuOptions and any record in MenuAccess with a specific fkGroupid. For example: ... IIf I run a query for fkgroupid = 1 I should get AccessId fkMenuID fkgroupid view execute MenuId MenuName 1 1 2 1 1 1 Order Maintenence Null Null Null Null Null 2 Customer Maintenence Null Null Null Null Null 3 Reports You'll need a UNION for: SELECT ma.AccessId, ma.fkMenuID, ma.fkgroupid, ma.view, ma.execute, mo.MenuId, mo.MenuName FROM MenuAccess AS ma LEFT JOIN MenuOptions AS mo ON mo.Menuid = ma.fkMenuID WHERE ma.fkgroupid = 1 UNION SELECT NULL, NULL, NULL, NULL, NULL, MenuID, MenuName FROM MenuOptions WHERE MenuID NOT IN (SELECT fkMenuID FROM MenuAccess WHERE fkgroupid = 1); (untested but it looks about right) Note that the second WHERE clause has a subquery where you have to repeat the given fkgroupid. Otherwise, you'll get 2 rows for Order Maintenence. I'm sure there's a much more elegant way to achieve this. As an aside, you really should pick one column-naming convention and stick to it. The case of the column names is all over the place (fkMenuID, fkgroupid, MenuID). This can only cause you headaches down the road when you're trying to remember if it was MenuID, menuId, menuid, or something altogether different. Personally, I use fk_menu_id. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Rhino wrote: I'm glad the explanation helped. I figured that the solution alone wouldn't be very useful if it wasn't explained since it is not obvious to most people how correlated queries work :-) I really wasn't trying to center you out with my rant about MySQL version. It's been a long-running frustration for me; I've tried to urge people to give this information in their notes to the list but an awful lot of people never think to mention their version. Then, an already lengthy reply gets even longer as you try to explain: If you have Version 3.x, the solution is this If you have Version 4.x the solution is this. If you have Version 5.x, the solution is this. You get the picture. Okay, let me explain the temp tables approach now. Most subqueries are not correlated so the subquery gets done once and then its result gets plugged into the outer query. For example, given a table called Employee in the Sample database which has one row for each employee and each row contains an employee number, last name, and salary, this query will return the employee number and last name of every employee who makes more than the average salary for all employees in the table: --- select empno, lastname from Sample.Employee where salary (select avg(salary) from Sample.Employee); --- If subqueries are supported, as in Version 4.1.x or above of MySQL, you just run that exact query and you will get a list of all of the people who make more than the average salary. However, if you are using an older version of MySQL which doesn't support subqueries, you need to use a temporary table approach. Basically, you create a temporary table that contains the same results that the subquery would have given you, then join it to the outer query. [Many, but not all, subqueries can be rewritten as joins.] For example: --- #Create the temporary table that will contain the average salary for all employees. create table if not exists temp_average_salary (average_salary decimal(9,2)); #Populate the temporary table with the average salary for all employees. insert into temp_average_salary(select avg(salary) from Sample.Employee); #Do a join of the employee table and the temporary table to obtain only employees who make #more than the average salary. select empno, lastname, salary from Sample.Employee e inner join temp_average_salary t where e.salary t.average_salary; --- This would give the same result as the original solution that used subqueries although there is obviously more work to do in accomplishing the desired result. Initially, I was almost certain that this approach could not be made to work for your problem due to its correlated nature but a couple of experiments persuaded me that it was possible after all. It's actually quite similar to the example I gave above, except that this time the temporary table is not just a single value on a single row but a table containing one row for each VBS_id, showing the max (latest) date for any row containing that VBS_id. Here's the definition of the table and the correct method of populating the table: --- create table if not exists temp_table (VBS_id int not null, max_date date not null); insert into temp_table (select VBS_id, max(date) from VBS_table group by VBS_id); --- If you display the contents of the temporary table, you get: --- VBS_idmax_date 112005-10-08 122005-10-08 --- Now, you simply join the VBS_table and the temp_table, as follows: --- select v.VBS_id, v.date, v.weight from VBS_table v inner join temp_table t on v.VBS_id = t.VBS_id and v.date = t.max_date; --- The result is exactly what you wanted: --- VBS_iddateweight 112005-10-0811.52 122005-10-0810.50 --- You could apply this solution to versions of MySQL that don't have subquery support; just remember that you need to re-create the temp_table immediately before you do the join each time; otherwise, you are going to determine whatever weights were in effect whenever you first populated the temp_table, not the current weights. In your case though, you can make life better for yourself. Since you have view support, you can simply create a view that is the equivalent to the temp_table and then join the view to the VBS_table to get the data you want. Since the view is always up-to-date, you eliminate the need to have the temp_table at all. Something like this: --- create view VBS_max_dates (VBS_id, max_date) as select VBS_id, max(date) from VBS_table group by VBS_id; select v.VBS_id, v.date, v.weight from VBS_table v inner join VBS_max_dates z on v.VBS_id = z.VBS_id and v.date = z.max_date; --- Note that the view is only created once, not each time you are about to do the join!! One other solution jumps to mind that should be just as good. Instead of creating temp tables or views, why not just put the original correlated subquery that I gave you into a stored procedure? Then, all you'd need to do is call that stored procedure every
Re: Help with an SQL query
Rhino wrote: I can't test this in MySQL- I'm using an older version of MySQL that doesn't support subqueries - but it works in DB2 and it should do the trick for getting the current weight of each VBS_id value: select VBS_id, date, weight from VBS_table x where date = (select max(date) from VBS_table where VBS_id = x.VBS_id); I'm assuming you are using a more version of MySQL which DOES support subqueries! If not, you may be able to get the same result with temp tables but I haven't tried that. Make sure to say something if you don't know how to use temp tables to simulate subqueries. I'm dubious that this query can be simulated with temp tables though (Rant: I REALLY wish people (all people, not just you) posting questions to this mailing list would get in the habit of specifying which version of MySQL they are using!! The answers to questions FREQUENTLY depend on the MySQL version so it would really help reduce the size of answers if people volunteered this information in the original question.) Also, I'm assuming that that MySQL supports correlated subqueries; I'm really not sure so I'll let you try the actual query and see if it works for you. Here's an explanation of how this query works, in case you've never seen one like this before. This is called a correlated subquery; the key thing that makes it obvious that this is a correlated subquery (in case you have to recognize one) is that a correlation name, in this case 'x', appears in the FROM clause of the outer query and again in the WHERE clause of the subquery. The subquery appears in brackets in case you are not familiar with subqueries. A correlated subquery works backwards to a normal subquery. In a normal subquery, the subquery is executed first and is only executed once: its result is plugged into the outer query which then executes just once as well. In a correlated subquery, both the outer query and the subquery are executed _repeatedly_, possibly MANY times each. The outer query is executed in order to obtain one row, THEN the subquery is executed to see if the row found by the outer query can be kept. In this case, let's say that the outer query returned the first row of the table, which has a VBS_id of 11 and a date of '10/3/2005': the subquery determines the maximum (most recent) date for any row that has same VBS_id as the one just found by the outer query; if the maximum date differs from the date found by the outer query, the outer query row is discarded and does not appear in the final result set. In this case, the maximum date for VBS_ID is 10/8/2005 which is not equal to the value found by the outer query, so that row is discarded. Having dealt with the first row of the outer query, the outer query executes again and gets another row. Again, the subquery is executed to see if the date is the same as maximum date for that VBS_id and again, the outer row is only kept if its date matches the maximum date found by the subquery. And so it goes, one row at a time, until the outer query has read every row of the table; a single row of the outer query is obtained, then the subquery determines if that row contains the maximum date for the VBS_id that was just found by the outer query. The final result will contain only rows that have the maximum dates for each VBS_id and will show the VBS_id, the maximum date, and the weight at that date. That is the result you requested. Bear in mind that a correlated query can perform much more poorly than a regular subquery, although the optimizer sometimes has tricks that make the performance quite tolerable. There may be a solution that doesn't involve a correlated subquery but it's not coming to me. However, others who are better at SQL may think of something that does the job. Rhino Wow, excellent explanation. By the way, my apologies... I am using MySQL 5.0.15. I would be interested in knowing how to use temp tables to simulate subqueries though. I found out that the sql statement, although it returns the correct result, does not work in a view because MySQL Views does not allow subqueries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
that result. Rhino - Original Message - From: Gobi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, November 05, 2005 8:05 AM Subject: Re: Help with an SQL query Rhino wrote: I can't test this in MySQL- I'm using an older version of MySQL that doesn't support subqueries - but it works in DB2 and it should do the trick for getting the current weight of each VBS_id value: select VBS_id, date, weight from VBS_table x where date = (select max(date) from VBS_table where VBS_id = x.VBS_id); I'm assuming you are using a more version of MySQL which DOES support subqueries! If not, you may be able to get the same result with temp tables but I haven't tried that. Make sure to say something if you don't know how to use temp tables to simulate subqueries. I'm dubious that this query can be simulated with temp tables though (Rant: I REALLY wish people (all people, not just you) posting questions to this mailing list would get in the habit of specifying which version of MySQL they are using!! The answers to questions FREQUENTLY depend on the MySQL version so it would really help reduce the size of answers if people volunteered this information in the original question.) Also, I'm assuming that that MySQL supports correlated subqueries; I'm really not sure so I'll let you try the actual query and see if it works for you. Here's an explanation of how this query works, in case you've never seen one like this before. This is called a correlated subquery; the key thing that makes it obvious that this is a correlated subquery (in case you have to recognize one) is that a correlation name, in this case 'x', appears in the FROM clause of the outer query and again in the WHERE clause of the subquery. The subquery appears in brackets in case you are not familiar with subqueries. A correlated subquery works backwards to a normal subquery. In a normal subquery, the subquery is executed first and is only executed once: its result is plugged into the outer query which then executes just once as well. In a correlated subquery, both the outer query and the subquery are executed _repeatedly_, possibly MANY times each. The outer query is executed in order to obtain one row, THEN the subquery is executed to see if the row found by the outer query can be kept. In this case, let's say that the outer query returned the first row of the table, which has a VBS_id of 11 and a date of '10/3/2005': the subquery determines the maximum (most recent) date for any row that has same VBS_id as the one just found by the outer query; if the maximum date differs from the date found by the outer query, the outer query row is discarded and does not appear in the final result set. In this case, the maximum date for VBS_ID is 10/8/2005 which is not equal to the value found by the outer query, so that row is discarded. Having dealt with the first row of the outer query, the outer query executes again and gets another row. Again, the subquery is executed to see if the date is the same as maximum date for that VBS_id and again, the outer row is only kept if its date matches the maximum date found by the subquery. And so it goes, one row at a time, until the outer query has read every row of the table; a single row of the outer query is obtained, then the subquery determines if that row contains the maximum date for the VBS_id that was just found by the outer query. The final result will contain only rows that have the maximum dates for each VBS_id and will show the VBS_id, the maximum date, and the weight at that date. That is the result you requested. Bear in mind that a correlated query can perform much more poorly than a regular subquery, although the optimizer sometimes has tricks that make the performance quite tolerable. There may be a solution that doesn't involve a correlated subquery but it's not coming to me. However, others who are better at SQL may think of something that does the job. Rhino Wow, excellent explanation. By the way, my apologies... I am using MySQL 5.0.15. I would be interested in knowing how to use temp tables to simulate subqueries though. I found out that the sql statement, although it returns the correct result, does not work in a view because MySQL Views does not allow subqueries. -- 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 incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with an SQL query
Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight -- Arno Coetzee Developer Flash Media Group Office : +27 12 342 3490 Mobile : +27 82 693 6180 Fax : + 27 12 430 4269 www.flashmedia.co.za FMG Total messaging solution. For all your GSM (SMS and USSD) messaging needs. Quick and easy do-it-yourself interfaces. http://www.flashmedia.co.za/tms.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Arno Coetzee wrote: Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight Actually, I tried that. For one thing, using max() also requires a GROUP BY clause. Otherwise, mysql complains. So I am forced to run the following statement: Select VBS_ID, max(Date) from Weight Group By VBS_ID And it returns: VBS_IDmax(Date)weight 1110/8/200511.5 1210/8/200510.5 At first, I thought I had it right but when I look more closely, the weights are incorrect. The weight for 11 on 10/8/2005 should be 11.52. It looks look it is just returning the weight for 10/3/2005. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Gobi wrote: Arno Coetzee wrote: Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight Actually, I tried that. For one thing, using max() also requires a GROUP BY clause. Otherwise, mysql complains. So I am forced to run the following statement: Select VBS_ID, max(Date) from Weight Group By VBS_ID And it returns: VBS_IDmax(Date)weight 1110/8/200511.5 1210/8/200510.5 At first, I thought I had it right but when I look more closely, the weights are incorrect. The weight for 11 on 10/8/2005 should be 11.52. It looks look it is just returning the weight for 10/3/2005. As a further note on my above post, I added idx to my query: Select idx, VBS_ID, max(Date) from Weight Group By VBS_ID and it returned: idxVBS_IDmax(Date)weight 1 1110/8/200511.5 5 1210/8/200510.5 So actually, it is returning the wrong rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Hi Gobi, there was a similar posting in august. See: http://lists.mysql.com/mysql/187436 which I think describes what you want. I'll include a bit of it here as well -- This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; - /Johan Gobi wrote: Gobi wrote: Arno Coetzee wrote: Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight Actually, I tried that. For one thing, using max() also requires a GROUP BY clause. Otherwise, mysql complains. So I am forced to run the following statement: Select VBS_ID, max(Date) from Weight Group By VBS_ID And it returns: VBS_IDmax(Date)weight 1110/8/200511.5 1210/8/200510.5 At first, I thought I had it right but when I look more closely, the weights are incorrect. The weight for 11 on 10/8/2005 should be 11.52. It looks look it is just returning the weight for 10/3/2005. As a further note on my above post, I added idx to my query: Select idx, VBS_ID, max(Date) from Weight Group By VBS_ID and it returned: idxVBS_IDmax(Date)weight 1 1110/8/200511.5 5 1210/8/200510.5 So actually, it is returning the wrong rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Johan Höök wrote: Hi Gobi, there was a similar posting in august. See: http://lists.mysql.com/mysql/187436 which I think describes what you want. I'll include a bit of it here as well -- This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; - /Johan Sorry for being slow but this statement is quite confusing and I am having difficulty trying to understand it in order to adapt it to my table. Can you explain it? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Johan Höök wrote: Hi Gobi, there was a similar posting in august. See: http://lists.mysql.com/mysql/187436 which I think describes what you want. I'll include a bit of it here as well -- This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; - /Johan Actually, taking a closer look at your link, I think my query is slightly different from Kemin's. Kemin was trying to get the top numbers from col2 AND col3 together. Therefore, in his original statement, he was trying to order both col2 and col3 in descending order. Me, I just want to get the most recent date for each unique VBS_ID. I don't need to get the max weight so I am not sure if it is necessary to concat my date and weight together. Comments? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Hi Gobi, the problem with your original query is that there is no guarantee that your max(date) and it's associated VBS_ID is picked, so what you have to ensure is that they get picked together, so I think your statement should be like this: SELECT VBS_ID, SUBSTRING( MAX( CONCAT( LPAD( Date,10,'0'),weight, 11 ) AS D LEFT( MAX( CONCAT( LPAD(Date,10,'0'),weight)), 10 ) AS W GROUP BY VBS_ID; What happens here is that you're not doing max on date, you're doing it on the combination of date and weight. What might be a problem though is your dateformat, you might run into problems with that I guess. Also you might have to play with lengths a bit. /Johan Gobi wrote: Johan Höök wrote: Hi Gobi, there was a similar posting in august. See: http://lists.mysql.com/mysql/187436 which I think describes what you want. I'll include a bit of it here as well -- This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; - /Johan Sorry for being slow but this statement is quite confusing and I am having difficulty trying to understand it in order to adapt it to my table. Can you explain it? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Figured out the query: select idx, vbs_id, date, weight from Weight, (select vbs_id as maxid, max(date) as maxdate from Weight group by vbs_id) as t where vbs_id = maxid and date = maxdate; It returns the proper weight and idx. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Help with an SQL query
Oops, I meant to post this on the list AND copy the person asking the question Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: Gobi [EMAIL PROTECTED] Sent: Friday, November 04, 2005 1:46 PM Subject: Re: Help with an SQL query I can't test this in MySQL- I'm using an older version of MySQL that doesn't support subqueries - but it works in DB2 and it should do the trick for getting the current weight of each VBS_id value: select VBS_id, date, weight from VBS_table x where date = (select max(date) from VBS_table where VBS_id = x.VBS_id); I'm assuming you are using a more version of MySQL which DOES support subqueries! If not, you may be able to get the same result with temp tables but I haven't tried that. Make sure to say something if you don't know how to use temp tables to simulate subqueries. I'm dubious that this query can be simulated with temp tables though (Rant: I REALLY wish people (all people, not just you) posting questions to this mailing list would get in the habit of specifying which version of MySQL they are using!! The answers to questions FREQUENTLY depend on the MySQL version so it would really help reduce the size of answers if people volunteered this information in the original question.) Also, I'm assuming that that MySQL supports correlated subqueries; I'm really not sure so I'll let you try the actual query and see if it works for you. Here's an explanation of how this query works, in case you've never seen one like this before. This is called a correlated subquery; the key thing that makes it obvious that this is a correlated subquery (in case you have to recognize one) is that a correlation name, in this case 'x', appears in the FROM clause of the outer query and again in the WHERE clause of the subquery. The subquery appears in brackets in case you are not familiar with subqueries. A correlated subquery works backwards to a normal subquery. In a normal subquery, the subquery is executed first and is only executed once: its result is plugged into the outer query which then executes just once as well. In a correlated subquery, both the outer query and the subquery are executed _repeatedly_, possibly MANY times each. The outer query is executed in order to obtain one row, THEN the subquery is executed to see if the row found by the outer query can be kept. In this case, let's say that the outer query returned the first row of the table, which has a VBS_id of 11 and a date of '10/3/2005': the subquery determines the maximum (most recent) date for any row that has same VBS_id as the one just found by the outer query; if the maximum date differs from the date found by the outer query, the outer query row is discarded and does not appear in the final result set. In this case, the maximum date for VBS_ID is 10/8/2005 which is not equal to the value found by the outer query, so that row is discarded. Having dealt with the first row of the outer query, the outer query executes again and gets another row. Again, the subquery is executed to see if the date is the same as maximum date for that VBS_id and again, the outer row is only kept if its date matches the maximum date found by the subquery. And so it goes, one row at a time, until the outer query has read every row of the table; a single row of the outer query is obtained, then the subquery determines if that row contains the maximum date for the VBS_id that was just found by the outer query. The final result will contain only rows that have the maximum dates for each VBS_id and will show the VBS_id, the maximum date, and the weight at that date. That is the result you requested. Bear in mind that a correlated query can perform much more poorly than a regular subquery, although the optimizer sometimes has tricks that make the performance quite tolerable. There may be a solution that doesn't involve a correlated subquery but it's not coming to me. However, others who are better at SQL may think of something that does the job. Rhino - Original Message - From: Gobi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 04, 2005 3:05 AM Subject: Help with an SQL query Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com
Re: help with an SQL query
Michael Satterwhite wrote: - As it is, all I can suggest is to JOIN on all 90 tables - and hope MySQL can handle the query - and that you can type all of them without error. Note that if you use a UNION query as you suggest above, you will get the last login FOR EACH DAY - not the overall last loging. - MySQL will only allow you to join 32 tables in a single SQL statement. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with an SQL query
Hi, I need a bit of help with a mySQL query. I have a list of users in a text file called 'users': u655354 u687994 u696974 u728141 .. .. and I need to check the last date each user logged on to the proxy in the last 3 months. In my database, there is a table for the users: mysql desc user_table; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | ID| int(10) unsigned | | MUL | NULL| auto_increment | | uid | varchar(10) | | PRI | || +---+--+--+-+-++ 2 rows in set (0.00 sec) and a table (tYYMMDD) for each days log: mysql desc t20041209; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | uid | int(10) unsigned | | MUL | 0 | | | time | timestamp(14)| YES | | NULL| | | ip | int(10) unsigned | | MUL | 0 | | | urlid| int(10) unsigned | | MUL | 0 | | | timetaken| smallint(5) unsigned | YES | | 0 | | | cs_size | int(10) unsigned | YES | MUL | 0 | | | sc_size | int(10) unsigned | YES | MUL | 0 | | | method_ID| tinyint(3) unsigned | | | 0 | | | action_ID| tinyint(3) unsigned | | | 0 | | | virus_ID | tinyint(3) unsigned | | | 0 | | | useragent_ID | smallint(5) unsigned | | MUL | 0 | | +--+--+--+-+-+---+ 11 rows in set (0.00 sec) The time column here gives the actual time the user logged on, but I would be happy just to know the date (which I could get from the table name if the user's uid was present in there). Could anyone help me to write an SQL query to do this please? Many thanks. Please let me know if you need anymore information. JS. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with an SQL query
J S wrote: | Hi, | | I need a bit of help with a mySQL query. I have a list of users in a | text file called 'users': | | u655354 | u687994 | u696974 | u728141 | .. | .. | | and I need to check the last date each user logged on to the proxy in | the last 3 months. | | In my database, there is a table for the users: | | mysql desc user_table; | +---+--+--+-+-++ | | Field | Type | Null | Key | Default | Extra | | +---+--+--+-+-++ | | ID| int(10) unsigned | | MUL | NULL| auto_increment | | | uid | varchar(10) | | PRI | || | +---+--+--+-+-++ | 2 rows in set (0.00 sec) | | and a table (tYYMMDD) for each days log: | | mysql desc t20041209; | +--+--+--+-+-+---+ | | Field| Type | Null | Key | Default | Extra | | +--+--+--+-+-+---+ | | uid | int(10) unsigned | | MUL | 0 | | | | time | timestamp(14)| YES | | NULL| | | | ip | int(10) unsigned | | MUL | 0 | | | | urlid| int(10) unsigned | | MUL | 0 | | | | timetaken| smallint(5) unsigned | YES | | 0 | | | | cs_size | int(10) unsigned | YES | MUL | 0 | | | | sc_size | int(10) unsigned | YES | MUL | 0 | | | | method_ID| tinyint(3) unsigned | | | 0 | | | | action_ID| tinyint(3) unsigned | | | 0 | | | | virus_ID | tinyint(3) unsigned | | | 0 | | | | useragent_ID | smallint(5) unsigned | | MUL | 0 | | | +--+--+--+-+-+---+ | 11 rows in set (0.00 sec) | | The time column here gives the actual time the user logged on, but I | would be happy just to know the date (which I could get from the table | name if the user's uid was present in there). | | Could anyone help me to write an SQL query to do this please? | | Many thanks. Please let me know if you need anymore information. Select u.uid, max(l.time) as lastLog ~ From user_table u join t20041209 l on u.uid = l.uid ~ Group by uid; Thanks Mike. I need to run this query over 3 months though. Is there a quick way to write: t20041101 union t20041102 union t20041103 union ... t20050125 union t20050126 ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with an SQL query
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 J S wrote: | | | J S wrote: | | Hi, | | | | I need a bit of help with a mySQL query. I have a list of users in a | | text file called 'users': | | | | u655354 | | u687994 | | u696974 | | u728141 | | .. | | .. | | | | and I need to check the last date each user logged on to the proxy in | | the last 3 months. | | | | In my database, there is a table for the users: | | | | mysql desc user_table; | | +---+--+--+-+-++ | | | Field | Type | Null | Key | Default | Extra | | | +---+--+--+-+-++ | | | ID| int(10) unsigned | | MUL | NULL| auto_increment | | | | uid | varchar(10) | | PRI | || | | +---+--+--+-+-++ | | 2 rows in set (0.00 sec) | | | | and a table (tYYMMDD) for each days log: | | | | mysql desc t20041209; | | +--+--+--+-+-+---+ | | | Field| Type | Null | Key | Default | Extra | | | +--+--+--+-+-+---+ | | | uid | int(10) unsigned | | MUL | 0 | | | | | time | timestamp(14)| YES | | NULL| | | | | ip | int(10) unsigned | | MUL | 0 | | | | | urlid| int(10) unsigned | | MUL | 0 | | | | | timetaken| smallint(5) unsigned | YES | | 0 | | | | | cs_size | int(10) unsigned | YES | MUL | 0 | | | | | sc_size | int(10) unsigned | YES | MUL | 0 | | | | | method_ID| tinyint(3) unsigned | | | 0 | | | | | action_ID| tinyint(3) unsigned | | | 0 | | | | | virus_ID | tinyint(3) unsigned | | | 0 | | | | | useragent_ID | smallint(5) unsigned | | MUL | 0 | | | | +--+--+--+-+-+---+ | | 11 rows in set (0.00 sec) | | | | The time column here gives the actual time the user logged on, but I | | would be happy just to know the date (which I could get from the table | | name if the user's uid was present in there). | | | | Could anyone help me to write an SQL query to do this please? | | | | Many thanks. Please let me know if you need anymore information. | | Select u.uid, max(l.time) as lastLog | ~ From user_table u join t20041209 l on u.uid = l.uid | ~ Group by uid; | | | Thanks Mike. I need to run this query over 3 months though. Is there a | quick way to write: | | t20041101 union t20041102 union t20041103 union ... t20050125 union | t20050126 Not that I know of. Why are you using a different table per day - as opposed to a single log table with a field containing the day? I'm sure there's a reason for the current structure, but having a logday field in a single log table would make this kind of query much easier. A simple BETWEEN clause would suffice if it was in a single table. As it is, all I can suggest is to JOIN on all 90 tables - and hope MySQL can handle the query - and that you can type all of them without error. Note that if you use a UNION query as you suggest above, you will get the last login FOR EACH DAY - not the overall last loging. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCGhfOjeziQOokQnARAvSUAJ4zyHmYa95o+0eZ2zs//S24n0kyqQCeO6M2 UzELKfj6hZ14bp+NLLj+McQ= =NZBB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with an SQL query
In article [EMAIL PROTECTED], J S [EMAIL PROTECTED] writes: and I need to check the last date each user logged on to the proxy in the last 3 months. In my database, there is a table for the users: mysql desc user_table; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | ID| int(10) unsigned | | MUL | NULL| auto_increment | | uid | varchar(10) | | PRI | || +---+--+--+-+-++ 2 rows in set (0.00 sec) and a table (tYYMMDD) for each days log: mysql desc t20041209; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | uid | int(10) unsigned | | MUL | 0 | | | time | timestamp(14)| YES | | NULL| | | ip | int(10) unsigned | | MUL | 0 | | | urlid| int(10) unsigned | | MUL | 0 | | | timetaken| smallint(5) unsigned | YES | | 0 | | | cs_size | int(10) unsigned | YES | MUL | 0 | | | sc_size | int(10) unsigned | YES | MUL | 0 | | | method_ID| tinyint(3) unsigned | | | 0 | | | action_ID| tinyint(3) unsigned | | | 0 | | | virus_ID | tinyint(3) unsigned | | | 0 | | | useragent_ID | smallint(5) unsigned | | MUL | 0 | | +--+--+--+-+-+---+ 11 rows in set (0.00 sec) The time column here gives the actual time the user logged on, but I would be happy just to know the date (which I could get from the table name if the user's uid was present in there). Could anyone help me to write an SQL query to do this please? Create a MERGE table over all the logs for at least the last three months, and then proceed as described in the manual under The Rows Holding the Group-wise Maximum of a Certain Field. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help on a SQL query: need LEFT JOIN?
You wrote: I apologize for asking such a basic SQL question, but I am failing in finding the solution... Consider the following table: id | option --- 1 | a 2 | a 2 | b I want to find the ids with only a specific set of options. For example, if I wanted to get the ids which have option a only, the query should give me 1 as a result, not 1 and 2. I suspect I need to use a LEFT JOIN on the same table, but I have failed miserably so far... Many thanks in advance to the SQL experts ;-). Jean-Luc (http://jfontain.free.fr/moodss/) There are no need to use the left join in this query. The best way to do what you wanna is within the following query: SELECT id FROM table GROUP BY id HAVING COUNT(option) = 1; You can change the count value to take the ids that have specified number of options. Luís Fernando - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: need help on a SQL query: need LEFT JOIN?
I wrote: I apologize for asking such a basic SQL question, but I am failing in finding the solution... Consider the following table: id | option --- 1 | a 2 | a 2 | b I want to find the ids with only a specific set of options. For example, if I wanted to get the ids which have option a only, the query should give me 1 as a result, not 1 and 2. I suspect I need to use a LEFT JOIN on the same table, but I have failed miserably so far... Many thanks in advance to the SQL experts ;-). Thank you very much to all for your responses: it led me to the solution and in the process improved my SQL level: select id from t left join t as u on (t.id = u.id) and (t.option = u.option) and ( (t.option = 'a') or (t.option = 'b') or ... ) group by t.id having (count(*) = count(u.id)) and (count(*) = N) * with N = number of options to match Thanks again! Jean-Luc (http://jfontain.free.fr/moodss/) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
need help on a SQL query: need LEFT JOIN?
I apologize for asking such a basic SQL question, but I am failing in finding the solution... Consider the following table: id | option --- 1 | a 2 | a 2 | b I want to find the ids with only a specific set of options. For example, if I wanted to get the ids which have option a only, the query should give me 1 as a result, not 1 and 2. I suspect I need to use a LEFT JOIN on the same table, but I have failed miserably so far... Many thanks in advance to the SQL experts ;-). Jean-Luc (http://jfontain.free.fr/moodss/) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: need help on a SQL query: need LEFT JOIN?
Does this work? select t.id,t.option,t2.option from tableName t left join tableName t2 on t.id=t2.id and t2.option!=t.option where t.option=a and isnull(t2.option); HTH, Jed On the threshold of genius, [EMAIL PROTECTED] wrote: I apologize for asking such a basic SQL question, but I am failing in finding the solution... Consider the following table: id | option --- 1 | a 2 | a 2 | b I want to find the ids with only a specific set of options. For example, if I wanted to get the ids which have option a only, the query should give me 1 as a result, not 1 and 2. I suspect I need to use a LEFT JOIN on the same table, but I have failed miserably so far... Many thanks in advance to the SQL experts ;-). Jean-Luc (http://jfontain.free.fr/moodss/) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with complex SQL Query
Greetings: Prelude: We have five tables all sharing a very similar structure (each has a Customer_ID and Server_ID field for example). Each table can contain zero to many records with duplicate Server_ID values allowed. Need: I need to be able to count the distinct Server_ID's across all of the tables. Problems: A customer can have one Server_ID in one table, ten Server_ID's (some duplicate) in another table and so on. The customer will have the same Customer_ID in all tables. I've tried inner joins, left joins, et all to no avail. The syntax below will only show two Server_ID's for a customer that has close to 10. SELECT DISTINCT Application.Server_ID FROM Application LEFT JOIN Content ON Application.Customer_ID = Content.Customer_ID LEFT JOIN Performance ON Content.Customer_ID = Performance.Customer_ID LEFT JOIN Ping ON Performance.Customer_ID = Ping.Customer_ID LEFT JOIN Port ON Ping.Customer_ID = Port.Customer_ID WHERE Application.Customer_ID = 1 OR Content.Customer_ID = 1 OR Performance.Customer_ID = 1 OR Ping.Customer_ID = 1 OR Port.Customer_ID = 1; What is the correct SQL to get a list of all of the distinct Server_ID's across the five tables? Thank you. Peter M. Perchansky, Microsoft FrontPage MVP Dynamic Net, Inc. Helping companies do business on the Net 420 Park Road; Suite 201 Wyomissing PA 19610 Non-Toll Free: 1-610-736-3795 Personal Email: [EMAIL PROTECTED] Company Email: [EMAIL PROTECTED] Web:http://www.dynamicnet.net/ http://www.wemanageservers.com/
