Re: Three tables join

tomeu.vizoso_at_axisdata.net (Tomeu) wrote in message news:<bb6e5aca.0204090650.56686d75_at_posting.google.com>...
> Hello,
>
> 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?

something like:

select t1.tocode, (select sum(paxes) from hbooking where tocode = t1.tocode) as hpaxes, (select sum(paxes) from tbooking where tocode = t1.tocode) as tpaxes from toperator as t1

aught to work

Hope it helps
/Lennart Received on Tue Apr 09 2002 - 22:00:56 CEST