[GENERAL] noobie join question
Hi List, I am having trouble trying to figure out how to get the result listed at the bottom. I have 3 tables units, types of units which has a description of the units, and a table that list associations of the units. I can't figure out how to do the proper joins. Any pointers would be appreciated. create table types ( id integer, descr varchar(30) ); COPY types (id, descr) FROM stdin; 1descr 1 2descr 2 3descr 3 4descr 4 \. create table units ( uid integer, udevice varchar(30), utype integer ); COPY units (uid, udevice, utype) FROM stdin; 1a1 2b1 3c4 4d3 \. create table assoc ( aid integer, src_id integer, dest_id integer ); COPY assoc (aid, src_id, dest_id) FROM stdin; 112 213 334 442 \. desired result a | descr 1 | b | descr 1 a | descr 1 | c | descr 4 c | descr 4 | d | descr 3 d | descr 3 | b | descr 1 Thanks, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] noobie join question
On Mon, 2015-05-11 at 06:46 -0400, Steve Clark wrote: Hi List, I am having trouble trying to figure out how to get the result listed at the bottom. I have 3 tables units, types of units which has a description of the units, and a table that list associations of the units. I can't figure out how to do the proper joins. Any pointers would be appreciated. SELECT us.udevice, ts.descr, ud.udevice, td.descr FROM assoc AS a LEFT JOIN units AS us ON a.src_id = us.uid LEFT JOIN types AS ts ON us.utype = ts.id LEFT JOIN units AS ud ON a.dest_id = ud.uid LEFT JOIN types AS td ON ud.utype = td.id; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] noobie join question
Steve Clark wrote: I am having trouble trying to figure out how to get the result listed at the bottom. That's a bit tough, since you don't describe the desired result. I have 3 tables units, types of units which has a description of the units, and a table that list associations of the units. I can't figure out how to do the proper joins. Any pointers would be appreciated. create table types ( id integer, descr varchar(30) ); COPY types (id, descr) FROM stdin; 1descr 1 2descr 2 3descr 3 4descr 4 \. create table units ( uid integer, udevice varchar(30), utype integer ); COPY units (uid, udevice, utype) FROM stdin; 1a1 2b1 3c4 4d3 \. create table assoc ( aid integer, src_id integer, dest_id integer ); COPY assoc (aid, src_id, dest_id) FROM stdin; 112 213 334 442 \. These tables should have foreign key constraints to each other, so that we can understand how they are related and to make sure that no impossible values are inserted. desired result a | descr 1 | b | descr 1 a | descr 1 | c | descr 4 c | descr 4 | d | descr 3 d | descr 3 | b | descr 1 If my guesses are correct, the query would be SELECT u1.udevice, t1.descr, u1.udevice, t1.descr FROM assoc a JOIN units u1 ON (a.src_id = u1.uid) JOIN types t1 ON (u1.utype = t1.id) JOIN units u2 ON (a.dest_id = u2.uid) JOIN types t2 ON (u2.utype = t2.id); I did not test this. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] noobie join question
On 05/11/2015 07:16 AM, Oliver Elphick wrote: On Mon, 2015-05-11 at 06:46 -0400, Steve Clark wrote: Hi List, I am having trouble trying to figure out how to get the result listed at the bottom. I have 3 tables units, types of units which has a description of the units, and a table that list associations of the units. I can't figure out how to do the proper joins. Any pointers would be appreciated. SELECT us.udevice, ts.descr, ud.udevice, td.descr FROM assoc AS a LEFT JOIN units AS us ON a.src_id = us.uid LEFT JOIN types AS ts ON us.utype = ts.id LEFT JOIN units AS ud ON a.dest_id = ud.uid LEFT JOIN types AS td ON ud.utype = td.id; Thanks Oliver - that worked perfectly. -- Stephen Clark *NetWolves Managed Services, LLC.* Director of Technology Phone: 813-579-3200 Fax: 813-882-0209 Email: steve.cl...@netwolves.com http://www.netwolves.com
