Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. There is no such thing as a universal "id" -- to be
something is to be something in particular -- a row number or IDENTITY
is a way of destroying a RDBMS and making into a sequential file
system. Why do you have that silly, redundant "tbl-" prefix on data
element name -- tell me what it is LOGICALLY and not how you are
PHYSICALLY representing it. It makes you look like a FORTRAN or BASIC
programmer.
Let's get some DDL and fix the schema. An inventory class is an
attribute of an inventory item.
CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
name CHAR(15) NOT NULL,
..);
CREATE TABLE Inventory
(inventory_nbr INTEGER NOT NULL PRIMARY KEY,
inv_class CHAR(5) NOT NULL
CHECK (inv_class IN (..)),
item_description CHAR(15) NOT NULL,
..,);
CREATE TABLE Allocations
(person_id INTEGER NOT NULL
REFERENCES People(person_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
inventory_nbr INTEGER NOT NULL
REFERENCES Inventory (inventory_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
..,
PRIMARY KEY (person_id, inventory_nbr));
Can someone tell me how to write a query or a stored procedure
which produces a result set like this: <<
This is a report and not a query; this ought to be done in the front
end and not in the database at all. Thanks to the lack of specs and
DDL, we have no idea if people can have more than one car or more than
one phone. Here is one possible guess at an answer:
SELECT P1.name,
MAX (CASE WHEN I1.inv_class = 'car'
THEN I1.item_description
ELSE NULL END) AS auto,
MAX (CASE WHEN I1.inv_class = 'phone'
THEN I1.item_description
ELSE NULL END) AS phone
FROM People AS P1, Allocations AS A1, Inventory as I1
WHERE A1.person_id = P1.person_id
AND A1.inventory_nbr = I1.inventory_nbr
GROUP BY P1.name;
if you allowed only one item per class, then use:
CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
name CHAR(15) NOT NULL,
..);
CREATE TABLE Inventory
(inventory_nbr INTEGER NOT NULL PRIMARY KEY,
item_description CHAR(15) NOT NULL,
..,);
CREATE TABLE Allocations
(person_id INTEGER NOT NULL
REFERENCES People(person_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
inventory_nbr INTEGER NOT NULL
REFERENCES Inventory (inventory_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
inv_class CHAR(5) NOT NULL
CHECK (inv_class IN (..)),
UNIQUE (person_id, inv_class),
..,
PRIMARY KEY (person_id, inventory_nbr));
