Re: novice on SQL

[tony yau]

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" <johnlist@stripped> wrote in message
news:445FB394.10002@ style="color:#666">stripped...
> 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" <tony.yau@stripped> wrote in message
> > news:e3li07$pib$1@ style="color:#666">stripped...
> >> 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" <johnlist@stripped> wrote in message
> >> news:445E45DF.8020902@ style="color:#666">stripped...
> >>> 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
> >>>