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

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;
1 descr 1
2 descr 2
3 descr 3
4 descr 4
\.

create table units (
uid integer,
udevice varchar(30),
utype integer
);

COPY units (uid, udevice, utype) FROM stdin;
1 aaaaa 1
2 bbbbb 1
3 ccccc 4
4 ddddd 3
\.

create table assoc (
aid integer,
src_id integer,
dest_id integer
);

COPY assoc (aid, src_id, dest_id) FROM stdin;
1 1 2
2 1 3
3 3 4
4 4 2
\.

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
aaaaa | descr 1 | bbbbb | descr 1
aaaaa | descr 1 | ccccc | descr 4
ccccc | descr 4 | ddddd | descr 3
ddddd | descr 3 | bbbbb | 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

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.clark@netwolves.com
http://www.netwolves.com