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