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.siteid, tasktable.prices 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 siteid prices1 prices2 (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]
