Three tables join

From: Tomeu <tomeu.vizoso_at_axisdata.net>
Date: 9 Apr 2002 07:50:40 -0700
Message-ID: <bb6e5aca.0204090650.56686d75@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?

Thanks a lot. Received on Tue Apr 09 2002 - 09:50:40 CDT