Обсуждение: 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;
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
\.

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


Thanks,
Steve

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;

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

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;