Re: SQL Q: Proper and best way to join 3 tables?

Rob Williamson wrote:
> If table A is the parent table and B and C are children with the PK of A
> being a FK in each of B and C.
>
> I have tried:
> select A.pk,B.fk,C.fk
> from A,B,C
> where A.pk = B.fk
> and A.pk = C.fk;
>
> This does not seem to work.
>
> Basically I have an inventory table, a cost table and a comments table.
>
> The part_no key is the pk of Inventory and is also a foreign key in the
> other two tables. I am trying to make a query that returns all the
> Inventory with the Unit_Cost from the Cost table as well as a
> Manufacturer from the Comments table.
> I can do two tables but not 3.
> Help.
> Rob

It really should be quite simple (and just as you have done):-

SQL> create table inventory (part_no integer);

Table created.

SQL> create table cost (part_no integer, cost integer);

Table created.

SQL> create table comments (part_no integer, comments varchar2(10));

Table created.

SQL> insert into inventory values (1);

1 row created.

SQL> insert into inventory values (2);

1 row created.

SQL> insert into inventory values (3);

1 row created.

SQL> insert into cost values (1, 10);

1 row created.

SQL> insert into cost values (2, 15);

1 row created.

SQL> insert into cost values (3, 20);

1 row created.

SQL> insert into comments values (1, 'foobar');

1 row created.

SQL> insert into comments values (2, 'barfoo');

1 row created.

SQL> insert into comments values (3, 'whatever');

1 row created.

SQL> commit;

Commit complete.

SQL> select i.part_no, c.cost, com.comments   2 from inventory i, cost c, comments com   3 where i.part_no = c.part_no
  4 and i.part_no = com.part_no;

   PART_NO COST COMMENTS
---------- ---------- ----------

         1         10 foobar
         2         15 barfoo
         3         20 whatever

Now, I have a feeling that you are having problems as there can be a one to many relationship between part_no and comments:-

SQL> insert into comments values (1, 'comment');

1 row created.

SQL> / 1 row created.

SQL> select i.part_no, c.cost, com.comments   2 from inventory i, cost c, comments com   3 where i.part_no = c.part_no
  4 and i.part_no = com.part_no;

   PART_NO COST COMMENTS
---------- ---------- ----------

         1         10 foobar
         1         10 comment
         1         10 comment
         2         15 barfoo
         3         20 whatever

If this is the case, there is not way for you to get a single row per part_no, as there are multiple comments associated with it. Is this the problem you are getting?

Or perhaps there is no comment for some parts in the comments table, so you will get no rows for that part:-

SQL> delete from comments where part_no = 1;

3 rows deleted.

SQL> select i.part_no, c.cost, com.comments   2 from inventory i, cost c, comments com   3 where i.part_no = c.part_no
  4 and i.part_no = com.part_no;

   PART_NO COST COMMENTS
---------- ---------- ----------

         2         15 barfoo
         3         20 whatever

To fix that you will need to outer join on the comments table:-

SQL> select i.part_no, c.cost, com.comments   2 from inventory i, cost c, comments com   3 where i.part_no = c.part_no
  4 and i.part_no = com.part_no(+);

   PART_NO COST COMMENTS
---------- ---------- ----------

         1         10
         2         15 barfoo
         3         20 whatever

Stephen. Received on Tue Sep 27 2005 - 16:05:14 CDT