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.
