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.2 12.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]
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]
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: 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: 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]