Re: joins
От | Oliver Elphick |
---|---|
Тема | Re: joins |
Дата | |
Msg-id | 1038546152.1383.381.camel@linda.lfix.co.uk обсуждение исходный текст |
Ответ на | joins (Michiel Lange <michiel@minas.demon.nl>) |
Список | pgsql-novice |
On Thu, 2002-11-28 at 23:41, Michiel Lange wrote: > Joins never was my strongest point: > > If I have these tables > > CREATE TABLE Company( > cmp_id serial PRIMARY KEY NOT NULL, -- I know NOT NULL is overkill... > cmp_name varchar(30) > ); > > CREATE TABLE Workers( > wrk_id serial PRIMARY KEY NOT NULL, > wrk_name varchar(30) NOT NULL, > wrk_company1 int4 NOT NULL REFERENCES Company.cmp_id, > wrk_company2 int4 REFERENCES Company.cmp_id > ); Your REFERENCES syntax is wrong. It should be: ... REFERENCES Company (cmp_id) > And I want to show all workers with their company names. As the tables > suggest each Worker works at at least one company, but possibly at two... I > think I set up the tables right (at least I hope so, but I have a strong > feeling it does) > > How would I set up the SELECT. > > I think up this (on the fly, not the slightest idea if it would work or not): > SELECT wrk_id,wrk_name,cmp_name,cmp_name FROM ( Company INNER JOIN Company > ON (wrk_company1 = Company.cmp_id) very much clueless here...) Since you are naming the same table in two different joins, you have to name it twice with different aliases: SELECT w.wrk_id, w.wrk_name, c1.cmp_name, c2.cmp_name FROM workers AS w LEFT JOIN company AS c2 ON w.wrk_company2 = c2.cmp_id, company AS c1 WHERE w.wrk_company1 = c1.cmp_id; or SELECT w.wrk_id, w.wrk_name, c1.cmp_name, c2.cmp_name FROM workers AS w INNER JOIN company AS c1 ON w.wrk_company1 = c1.cmp_id LEFT JOIN company AS c2 ON w.wrk_company2 = c2.cmp_id; (NB: your mixed-case names for tables are folded to lower-case, since you didn't quote them in the CREATE TABLE statements.) > I would go for an outer join for the second company they may work for, as > this value may be NULL, and an inner join would cause unwanted results, for > it would only display those workers that work for two companies. This is a LEFT [OUTER] JOIB, > And how would it work if there was a third table involved, let's say > Car_brands (brand_id serial, brand_name varchar(30)). And the Worker has at > least one car, but at most two (find such an employer that wants to store > THAT :P) > so that would make for two more fields in the Workers table: > car_brand1 int4 NOT NULL REFERENCES Car_brands.brand_id, -- every worker > has at least one car... > car_brand2 int4 REFERENCES Car_brands.brand_id > Of course I would want to display the brand-names as well, and there is one > inner join and an outer join, that much I could figure out... (an outer > join, since the second car MAY be NULL...) how would this SELECT look like? It's exactly the same: SELECT w.wrk_id, w.wrk_name, c1.cmp_name, c2.cmp_name FROM workers AS w INNER JOIN company AS c1 ON w.wrk_company1 = c1.cmp_id LEFT JOIN company AS c2 ON w.wrk_company2 = c2.cmp_id INNER JOIN car_brands AS b1 ON w.car_brand1 = c1.brand_id LEFT JOIN car_brands AS c2 ON w.car_brand2 = c2.brand_id; provided that it is true that every worker has at least one car, which sounds unlikely unless having a car is a condition of employment. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Who shall ascend into the hill of the LORD? or who shall stand in his holy place? He that hath clean hands, and a pure heart..." Psalms 24:3,4
Вложения
В списке pgsql-novice по дате отправления: