Обсуждение: 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@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.
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.adams@vcontractor.co.za>
To: pgsql-novice@postgresql.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@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)
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)