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) 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
