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 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.