Re: Three tables join

Tomeu wrote:

> I have three tables, one master table and two more that depend on the other.
>
> Touroperator Hotel booking Transfer booking
> ------------ ------------- ----------------
> TO code TO code TO code
> Paxes Paxes
>
> What I want is this:
>
> Touroperator Hotel paxes Transfer paxes
> ------------ ----------- --------------
> TO1 12 15
> TO2 23 21
> TO3 12 14
>
> And I tried this:
>
> SELECT
> Touroperator."TO code",
> SUM("Hotel booking".Paxes),
> SUM("Transfer booking".Paxes)
> FROM
> Touroperator,
> "Hotel booking",
> "Transfer booking"
> WHERE
> Touroperator."TO code" = "Hotel booking"."TO code",
> Touroperator."TO code" = "Transfer booking"."TO code"
> GROUP BY
> Touroperador."TO code"
>
> But it gives higher values because the cartesian product has repeated lines.
>
> How should I do this?
>
> Thanks a lot.

Something like this? (Availability and syntax will vary depending on what product you're running...)

select tocode,

       ( select sum(paxes)
         from   hotelbooking b
         where  a.tocode = b.tocode
       )
       ( select sum(paxes)
         from   transferbooking c
         where  a.tocode = c.tocode
       )

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Tue Apr 09 2002 - 17:03:37 CEST