Re: can u do three tables in left join?

You can adjust the way the optimizer parses the query by changing
the order of parenthesis which will alter the order that the query
executes.

SELECT *
    FROM
(t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col))
OUTER JOIN t3 ON (t1.col = t3.col);

Someone with more experience needs to help you with the syntax of
your query. I was getting lost in the parenthesises and what you
were looking for. I would suggest getting some very simple joins
working. This would give you a little more experience with the syntax.

Ted

-----Original Message-----
From: "Duncan Adams (DNS)" <duncan(dot)adams(at)vcontractor(dot)co(dot)za>
To: pgsql-novice(at)postgresql(dot)org
Date: Mon, 4 Feb 2002 12:53:47 +0200
Subject: [NOVICE] can u do three tables in left join?

> hi all
>
> i'm trying to get 3 tables in a left join is this possible?
> please excuse the english and spelling not my natural language.
>
> postgres(at)vodanam:~ > psql -V
> psql (PostgreSQL) 7.1.3
>
> i found this.
>
> PostgreSQL 7.1 and later supports outer joins using the SQL
standard
> syntax.
> Here are two examples:
> SELECT *
> FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
> or
> SELECT *
> FROM t1 LEFT OUTER JOIN t2 USING (col);
> These identical queries join t1.col to t2.col, and also return any
> unjoined
> rows in t1 (those with no match in t2). A RIGHT join would add
unjoined
> rows
> of t2. A FULL join would return the matched rows plus all unjoined
rows
> from
> t1 and t2. The word OUTER is optional and is assumed in LEFT,
RIGHT,
> and
> FULL joins. Ordinary joins are called INNER joins.
>
> but i also have t3. t2(per_sys) and t3(personal) don't always have
> values
> for t1(system)
> t2 holds joining values for t1 and t3 as the systems have
> adminastrators,
> managers, 2nd call out, and hardware call out personal asinged
to them.
>
> I use the following sql query
>
> select
> distinct system.name,
> personal.surname ||' '|| personal.name as pername,
> building.building,
> floor.floor,
> location.ref,
> technology.tech,
> system.ip,
> system.key_system as id
> from floor, location, technology, per_sys, system left outer join
> personal on
> (system.key_system = per_sys.system_key *\ t1 joining
> t2
> and
> per_sys.per_key = personal.key_personal *\ t2 joining
> t3
> and
> per_sys.admin = 't') *\
> selecting the administrator for system
> where system.name IS NOT NULL
> and (
> (
> system.location_key = location.key_location
> and location.building_key = building.building_key
> and location.floor = floor.floor_key
> )
> and (
> system.net_type = technology.tech_key
> )
> )
> order BY building.building, system.name
> ;
>
> and get the folowing error
>
> NOTICE: Adding missing FROM-clause entry for table "per_sys"
> ERROR: JOIN/ON clause refers to "per_sys", which is not part of
JOIN
>
> as i'm quite new to sql i don't know if this is the best way of doing
> this.
>
> thanx.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo(at)postgresql(dot)org)