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@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@postgresql.org)



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)