Re: can u do three tables in left join?

thanks that helped.

playing around with that i also found that

from system
left outer join per_sys on
(system.key_system = per_sys.system_key and per_sys.admin = 't')

left outer join personal on
(per_sys.per_key = personal.key_personal)

also works.

thanx again.

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

> 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)

---------------------------(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)