> Hi John, right the problem boils down to this:
sitetable tasktable ID taskid taskid Changes ------------- --------------- 1 10 10 100 2 11 10 120 SELECT sitetable.ID, tasktable.Changes FROM sitetable,tasktable WHERE sitetable.taskid = tasktable.taskid; and get the following: ID Changes ---------------- 1 100 1 120 but what I need is the following format ID Changes1 Changes2 (limits of 5) ----------------------------------- 1 100 120 etc ps: a collegue said to me that DBs are not design to do what I wanted to do (in 1 sql query anyway). mmmm 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) > > > > > > ID Changes1 Changes2 Changes3 > > > --------------------------------------------------------- > > > 1 10.0 10.0 same as > > > 1 10.3 10.3 > > > 1 12.2 12.2 > > > 2 31.0 31.0 > > > 3 1.02 1.02 > > > 3 4.9 4.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;) > > >>>> > > >>>> ID Changes > > >>>> ----------------- > > >>>> 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') > > >>>> > > >>>> ID Changes1 Changes2 Changes3 (limits of 5) > > >>>> -------------------------------------------- > > >>>> 1 10.0 10.3 12.2 > > >>>> 2 31.0 > > >>>> 3 1.02 4.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]
