Very Computer

Hi.  I'm having trouble doing a query with these tables (I changed the table
names to something that might make sense to people)...

BOOKS----------CABINETS
     |                                |
     |                                |
UNION1                 UNION2
     |                                |
     |                                |
     ----------------------
                      |
               WEIGHT

Books go into cabinets, so those tables join with books having a foreign key
to the cabinetID.    The books weigh something and so do the cabinets.   The
weights, though, aren't available for all books and cabinets, so I created 2
union tables, and I'm storing the weights in the weights table.   Each union
table has 2 foreign keys, one to the table shown above it in the diagram,
and the other to the weight table.  The weight table has a weightID and a
weight value (x pounds).

I need a SQL Select statement that returns BOOKS.title, CABINETS.name (the
name of the cabinet holding the book), and the weight of the book and the
cabinet.   My problem is that I don't know how to return the weights.

Without the weights, I'd use this

SELECT  Books.Title, Cabinets.Name
FROM Books, Cabinets
WHERE Books.CabinetID = Cabinets.CabinetID

This would return a nice list of all books and the cabinets in which those
books belong.  I just don't know what to add to return the weight of the
book and the cabinet onto each row of the recordset.  If I add
"WEIGHT.pounds" to the Select list and join up "Union1.BookID =
Books.BookID" and "Union1.WeightID = Weight.WeightID", then I'd have the
weight of the book - but I'd still be missing the weight of the cabinet in
which that book belongs!   And besides, I need a query that will return the
weight of the book and/or the cabinet even if the other weight is not
available for that record.

I think I might need nested queries where I first grab each of the book
weights and the cabinet weights and then do a master query.  But I'm not
sure how that would work.

By the way, I'm using the union tables for normalization because not all
books have weights, and not all cabinets have weights.  The union table only
has an entry when a weight exists.   I know it would be simple to just store
book weights in the book table, and cabinet weights in the cabinet table,
but then I'd have a lot of blank/wasted fields.

I wouldn't bother with the extra tables in your example.  I'd simply add a
weight column to the Books table and to the Cabinets table.  However, here's
how you get what you want with your existing tables.

SELECT
  Books.Title,
  Cabinets.Name,
  BookWeight.Pounds AS BookPounds,
  CabWeight.Pounds AS CabinetPounds
FROM
  Books  inner join Cabinets on Books.CabinetID = Cabinets.CabinetID

  LEFT JOIN  Union1 on Books.BookID = Union1.BookID
  LEFT JOIN  Weight AS BookWeight on Union1.WeightID = BookWeight.WeightID

  LEFT JOIN Unoin2 on Cabinets.CabinetID = Union2.CabinetID
  LEFT JOIN Weight AS CabWeight on Union2.WeightID = CabWeight.WeightID

Notice my use of the "AS" keyword to provide two aliases for the Weight
table (the query will work just as well if the keyword is left out).
The left outer joins will solve your second problem of returning the book
and cabinet even if there is no weight.
Check out join syntax in books online.

--
Rich Dillon
Acadio Corporation
http://acadio.com

Quote:> Hi.  I'm having trouble doing a query with these tables (I changed the
table
> names to something that might make sense to people)...

> BOOKS----------CABINETS
>      |                                |
>      |                                |
> UNION1                 UNION2
>      |                                |
>      |                                |
>      ----------------------
>                       |
>                WEIGHT

> Books go into cabinets, so those tables join with books having a foreign
key
> to the cabinetID.    The books weigh something and so do the cabinets.
The
> weights, though, aren't available for all books and cabinets, so I created
2
> union tables, and I'm storing the weights in the weights table.   Each
union
> table has 2 foreign keys, one to the table shown above it in the diagram,
> and the other to the weight table.  The weight table has a weightID and a
> weight value (x pounds).

> I need a SQL Select statement that returns BOOKS.title, CABINETS.name (the
> name of the cabinet holding the book), and the weight of the book and the
> cabinet.   My problem is that I don't know how to return the weights.

> Without the weights, I'd use this

> SELECT  Books.Title, Cabinets.Name
> FROM Books, Cabinets
> WHERE Books.CabinetID = Cabinets.CabinetID

> This would return a nice list of all books and the cabinets in which those
> books belong.  I just don't know what to add to return the weight of the
> book and the cabinet onto each row of the recordset.  If I add
> "WEIGHT.pounds" to the Select list and join up "Union1.BookID =
> Books.BookID" and "Union1.WeightID = Weight.WeightID", then I'd have the
> weight of the book - but I'd still be missing the weight of the cabinet in
> which that book belongs!   And besides, I need a query that will return
the
> weight of the book and/or the cabinet even if the other weight is not
> available for that record.

> I think I might need nested queries where I first grab each of the book
> weights and the cabinet weights and then do a master query.  But I'm not
> sure how that would work.

> By the way, I'm using the union tables for normalization because not all
> books have weights, and not all cabinets have weights.  The union table
only
> has an entry when a weight exists.   I know it would be simple to just
store
> book weights in the book table, and cabinet weights in the cabinet table,
> but then I'd have a lot of blank/wasted fields.