Re: 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;
> 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