473,382 Members | 1,594 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

complex query / advice needed

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
Jul 20 '05 #1
4 2059
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
Jul 20 '05 #2
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));
Jul 20 '05 #3
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
Jul 20 '05 #4
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
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

39
by: | last post by:
I am trying to run the following agregate function in a parameterized query on Access2000: Min(.*sqr(./.)/) The query saved OK, but an attempt to run it results in the message: The expression...
4
by: Starbuck | last post by:
OK, first let me say that I am no DB person. But I have a user here who keeps getting this error whenever she does, whatever it is she does, with databases... A google search takes me to...
2
by: Pete | last post by:
Before I get started with the question, does anyone have a (single) good book recommendation for database design? Not an Access-specific book, but something geared toward helping me figure out...
8
by: Matt | last post by:
Hi all, Thank you for taking the time. I have a database with 45 tables on it. 44 tables are linked to a main table through a one to one relationship. My question is, is there no way i can...
5
by: Wired Hosting News | last post by:
I tried to be breif and give a scenario so as not to be overlooked because it was soooo long. Let me give you real world. I am a manufacturer of goods and produce 11 items that are distributed...
1
by: arun | last post by:
Query is too complex -------------------------------------------------------------------------------- Hi, I was trying to solve this problem since last two days but couldn't find any solution. ...
6
by: pippapippa | last post by:
I should be most grateful for a little advice. I have used Access 2000 & latterly 2002. Am about to upgrade since it is evident that documentation, tutorials etc are more readily available in...
2
by: sara | last post by:
I am helping a non-profit track their elder clients and care given to the clients. The organization would like a report that shows various info on ALL clients, such as: # in each town, # and...
3
by: BUmed | last post by:
Ok let me start from the start. I have a form that has question in it and the person chooses 0 1 2 -99 for each. The form then needs to add up the numbers for the sub categories in the form. For...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.