complex query / advice needed

[Christoph Bisping]

Hello!

I'm seeking advice on a rather complex type of query I need to build
in an Access ADP (SQL-Server 7). There are four tables:

tblPeople
ID(PK) PRENAME
---------------
1 Thomas
2 Frank
3 Chris

tblInventoryClasses
ID(PK) INVENTORYCLASS
----------------------
1 Car
2 Phone

tblInventoryItems
ID(PK) relInvClass(FK) ITEM
-----------------------------------
1 1 Dodge Viper
2 1 Chrysler
3 2 Nokia
4 2 Samsung

tblPeopleInventory
ID(PK) relPeople(FK) relInvItem(FK)
--------------------------------------
1 1 2
2 1 3
3 2 1
4 2 3
5 3 4

In this example the last table tells me that
Thomas owns a Chrysler (class Car) and a Nokia (class Phone).

Can someone tell me how to write a query or a stored procedure which
produces a resultset like this:

qryOwners
People Car Phone
-----------------------------
Thomas Chrysler Nokia
Frank Dodge Viper Nokia
Chris [NULL] Samsung

The main idea is that I need to be able to iterate such a collection.
It is guranteed that one "People" only owns one or zero "Car" and one
or zero "Phone".

I guess that it might be impossible to design a stored procedure with
such a variable amount of columns (in this case, each item from
tblInventoryClasses would mean another column).

Ary there any possibilities in accomplishing this without creating
temporary tables?

Any help would be really appreciated ;-)

Greetings,
Christoph Bisping

[Russell Bevers]

Christoph Bisping <bi******************@unikopie.de> wrote in message news:<85********************************@4ax.com>. ..

Hello!

I'm seeking advice on a rather complex type of query I need to build
in an Access ADP (SQL-Server 7). There are four tables:

tblPeople
ID(PK) PRENAME
---------------
1 Thomas
2 Frank
3 Chris

tblInventoryClasses
ID(PK) INVENTORYCLASS
----------------------
1 Car
2 Phone

tblInventoryItems
ID(PK) relInvClass(FK) ITEM
-----------------------------------
1 1 Dodge Viper
2 1 Chrysler
3 2 Nokia
4 2 Samsung

tblPeopleInventory
ID(PK) relPeople(FK) relInvItem(FK)
--------------------------------------
1 1 2
2 1 3
3 2 1
4 2 3
5 3 4

In this example the last table tells me that
Thomas owns a Chrysler (class Car) and a Nokia (class Phone).

Can someone tell me how to write a query or a stored procedure which
produces a resultset like this:

qryOwners
People Car Phone
-----------------------------
Thomas Chrysler Nokia
Frank Dodge Viper Nokia
Chris [NULL] Samsung
This particular query looks like:

CREATE VIEW qryOwners AS
SELECT
person.PRENAME AS People,
car.ITEM AS Car,
phone.ITEM AS Phone
FROM
tblPeople person
LEFT JOIN
(
tblInventoryClasses carClass
INNER JOIN tblInventoryItems car
ON carClass.INVENTORYCLASS = 'Car'
AND carClass.ID = car.relInvClass
INNER JOIN tblPeopleInventory carPerson
ON car.ID = carPerson.relInvItem
) ON person.ID = carPerson.relPeople
LEFT JOIN
(
tblInventoryClasses phoneClass
INNER JOIN tblInventoryItems phone
ON phoneClass.INVENTORYCLASS = 'Phone'
AND phoneClass.ID = phone.relInvClass
INNER JOIN tblPeopleInventory phonePerson
ON phone.ID = phonePerson.relInvItem
) ON person.ID = phonePerson.relPeople

The main idea is that I need to be able to iterate such a collection.
It is guranteed that one "People" only owns one or zero "Car" and one
or zero "Phone".

I guess that it might be impossible to design a stored procedure with
such a variable amount of columns (in this case, each item from
tblInventoryClasses would mean another column).

You cannot get each InventoryClass row to magically add a column to
the resultset unless you use a stored procedure that builds dynamic
SQL statements using the EXECUTE(string) command. That would be
qualified as UGLY code, though.

You can see the pattern though.

It's simpler/more efficient on the database side if you just join all
the tables together and use an ORDER BY clause to make your client
code easier to write.

-Russell

[--CELKO--]

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));

[Christoph Bisping]

ru*****@hotmail.com (Russell Bevers) wrote:

You cannot get each InventoryClass row to magically add a column to
the resultset unless you use a stored procedure that builds dynamic
SQL statements using the EXECUTE(string) command. That would be
qualified as UGLY code, though.

You can see the pattern though.

It's simpler/more efficient on the database side if you just join all
the tables together and use an ORDER BY clause to make your client
code easier to write.

-Russell

That was exactly what I (didn't) want to read...
After digging through hundreds of url's everything looks to me like
that's the only way to accomplish this. Anyway, thanks for your answer
which showed me that I definitely have to handle these things in the
frontend.

In fact, the overall processing performance seems to be good enough if
I just read all of these tables in order to build lookup-tables in my
application. Being familiar with UGLY code, your suggestion using
EXECUTE(strSQL) sounds like an alternative ;-)

Greetings,

Christoph Bisping

[Christoph Bisping]

jo*******@northface.edu (--CELKO--) wrote:

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.

Well, this wouldn't be a wrong statement. I do agree that I'm using
way to much of these "universal id"-cols in my tables which are surely
wasted.

As you might guess I'm quite inexperienced here and for now pure DDL
isn't one of my favorite languages but I'll see if I'm able to adapt
what you've written. But it seems to be much easier for me to go the
"sequential file system" way and do the complex People<->InventoryItem
mappings entirely at frontend level.

Thanks for your detailed explanation!

Greetings,

Christoph Bisping