Re: novice on SQL
Hi John, right the problem boils down to this: sitetable tasktable ID taskidtaskid Changes ---- 11010100 21110120 SELECT sitetable.ID, tasktable.Changes FROM sitetable,tasktable WHERE sitetable.taskid = tasktable.taskid; and get the following: IDChanges 1 100 1 120 but what I need is the following format ID Changes1 Changes2 (limits of 5) --- 1 100120etc ps: a collegue said to me that DBs are not design to do what I wanted to do (in 1 sql query anyway). I'm beginning to accept that comment :( Thanks John Tony John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hi John, tried your suggestion but I can't get it to work. This is because I don't know how to set conditions in the following clauses (because there isn't any) and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] What values do you want for Changes1, Changes2, etc.? (How are you selecting for them.) Post your SQL here if you need further help. --J the result I've got was similar to the following (note the ID is pkey of another table) IDChanges1Changes2Changes3 - 1 10.010.0same as 1 10.310.3 1 12.212.2 2 31.031.0 3 1.021.02 3 4.94.9 thanks for your help anyway Tony tony yau [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi John, I didn't know you can do that! (such a novice indeed!) Thank you for your reply, I will put it to the test first thing when i get back to the office tomo. Cheers John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hello, I can get a select result like the following: (SELECT ID,Changes FROM mytable WHERE somecondition;) IDChanges - 1 10.0 1 10.3 1 12.2 2 31.0 3 1.02 3 4.9 how can I get the above result sets into the following format (columns 'Changes1','Changes2',... are all from 'Changes') IDChanges1Changes2Changes3 (limits of 5) 1 10.010.312.2 2 31.0 3 1.024.9 I have got a method that works (I think) by first do a SELECT getting DISTINCT id values and then foreach of these ID I do another SELECT to get the Changes values and then just massage the display. Is there another way of doing this by using a single SQL query? There may be a simpler way, but this should work: select Table.ID, Table1.Changes as Changes1, Table2.Changes as Changes2, Table3.Changes as Changes3 from Table, Table as Table1, Table as Table2, Table as Table3 where Table.ID = Table1.ID and Table.ID = Table2.ID and Table.ID = Table3.ID and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] order by table.ID --J -- 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: novice on SQL
Hi John, right the problem boils down to this: sitetable tasktable ID taskidtaskid Changes ---- 11010100 21110120 SELECT sitetable.siteid, tasktable.prices FROM sitetable,tasktable WHERE sitetable.taskid = tasktable.taskid; and get the following: IDChanges 1 100 1 120 but what I need is the following format siteidprices1 prices2 (limits of 5) --- 1 100120etc ps: a collegue said to me that DBs are not design to do what I wanted to do (in 1 sql query anyway). I'm beginning to accept that comment :( Thanks John Tony John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hi John, tried your suggestion but I can't get it to work. This is because I don't know how to set conditions in the following clauses (because there isn't any) and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] What values do you want for Changes1, Changes2, etc.? (How are you selecting for them.) Post your SQL here if you need further help. --J the result I've got was similar to the following (note the ID is pkey of another table) IDChanges1Changes2Changes3 - 1 10.010.0same as 1 10.310.3 1 12.212.2 2 31.031.0 3 1.021.02 3 4.94.9 thanks for your help anyway Tony tony yau [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi John, I didn't know you can do that! (such a novice indeed!) Thank you for your reply, I will put it to the test first thing when i get back to the office tomo. Cheers John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hello, I can get a select result like the following: (SELECT ID,Changes FROM mytable WHERE somecondition;) IDChanges - 1 10.0 1 10.3 1 12.2 2 31.0 3 1.02 3 4.9 how can I get the above result sets into the following format (columns 'Changes1','Changes2',... are all from 'Changes') IDChanges1Changes2Changes3 (limits of 5) 1 10.010.312.2 2 31.0 3 1.024.9 I have got a method that works (I think) by first do a SELECT getting DISTINCT id values and then foreach of these ID I do another SELECT to get the Changes values and then just massage the display. Is there another way of doing this by using a single SQL query? There may be a simpler way, but this should work: select Table.ID, Table1.Changes as Changes1, Table2.Changes as Changes2, Table3.Changes as Changes3 from Table, Table as Table1, Table as Table2, Table as Table3 where Table.ID = Table1.ID and Table.ID = Table2.ID and Table.ID = Table3.ID and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] order by table.ID --J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: novice on SQL
Hi John, tried your suggestion but I can't get it to work. This is because I don't know how to set conditions in the following clauses (because there isn't any) and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] the result I've got was similar to the following (note the ID is pkey of another table) IDChanges1Changes2Changes3 - 1 10.010.0same as 1 10.310.3 1 12.212.2 2 31.031.0 3 1.021.02 3 4.94.9 thanks for your help anyway Tony tony yau [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi John, I didn't know you can do that! (such a novice indeed!) Thank you for your reply, I will put it to the test first thing when i get back to the office tomo. Cheers John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hello, I can get a select result like the following: (SELECT ID,Changes FROM mytable WHERE somecondition;) IDChanges - 1 10.0 1 10.3 1 12.2 2 31.0 3 1.02 3 4.9 how can I get the above result sets into the following format (columns 'Changes1','Changes2',... are all from 'Changes') IDChanges1Changes2Changes3 (limits of 5) 1 10.010.312.2 2 31.0 3 1.024.9 I have got a method that works (I think) by first do a SELECT getting DISTINCT id values and then foreach of these ID I do another SELECT to get the Changes values and then just massage the display. Is there another way of doing this by using a single SQL query? There may be a simpler way, but this should work: select Table.ID, Table1.Changes as Changes1, Table2.Changes as Changes2, Table3.Changes as Changes3 from Table, Table as Table1, Table as Table2, Table as Table3 where Table.ID = Table1.ID and Table.ID = Table2.ID and Table.ID = Table3.ID and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] order by table.ID --J -- 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]
novice on SQL
Hello, I can get a select result like the following: (SELECT ID,Changes FROM mytable WHERE somecondition;) IDChanges - 1 10.0 1 10.3 1 12.2 2 31.0 3 1.02 3 4.9 how can I get the above result sets into the following format (columns 'Changes1','Changes2',... are all from 'Changes') IDChanges1Changes2Changes3 (limits of 5) 1 10.010.312.2 2 31.0 3 1.024.9 I have got a method that works (I think) by first do a SELECT getting DISTINCT id values and then foreach of these ID I do another SELECT to get the Changes values and then just massage the display. Is there another way of doing this by using a single SQL query? any help or hints will be very much appreciated. Thanks -- Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: novice on SQL
Hi John, I didn't know you can do that! (such a novice indeed!) Thank you for your reply, I will put it to the test first thing when i get back to the office tomo. Cheers John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hello, I can get a select result like the following: (SELECT ID,Changes FROM mytable WHERE somecondition;) IDChanges - 1 10.0 1 10.3 1 12.2 2 31.0 3 1.02 3 4.9 how can I get the above result sets into the following format (columns 'Changes1','Changes2',... are all from 'Changes') IDChanges1Changes2Changes3 (limits of 5) 1 10.010.312.2 2 31.0 3 1.024.9 I have got a method that works (I think) by first do a SELECT getting DISTINCT id values and then foreach of these ID I do another SELECT to get the Changes values and then just massage the display. Is there another way of doing this by using a single SQL query? There may be a simpler way, but this should work: select Table.ID, Table1.Changes as Changes1, Table2.Changes as Changes2, Table3.Changes as Changes3 from Table, Table as Table1, Table as Table2, Table as Table3 where Table.ID = Table1.ID and Table.ID = Table2.ID and Table.ID = Table3.ID and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] order by table.ID --J -- 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: MySQL give up on a query after a certain length of time
Hi Guys, you can always set a max time out for your php script (php.ini) Tony - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, May 20, 2005 12:45 PM Subject: Re: MySQL give up on a query after a certain length of time Hello. I don't remember such way (except your transaction could abort due to lock timeout). Check with SHOW PROCESSLIST in what state your query hangs. Jacob Friis Larsen [EMAIL PROTECTED] wrote: Hello. Is there a way to have MySQL give up on a query after a certain length of t= ime? I use Debian Linux Sarge, MySQL 4.1 and Php 5. Sometimes I have queries that take forever although the same query returns fast at other times. Thanks, Jacob -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]