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

Reply via email to