By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,704 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Query within the same table

P: 1
Please help me creatng a query
I've got 1 table called PRODUCT containing all my hardwareitems
Some hardwareitems belongs to another hardwareitem. For example a monitor belongs to a PC. There could be up to 3 levels.

I wants to create a query which results all my hardwareitems and the hardwareitems to which they belongs.

Example table PRODUCT
(the level-number (L1, L2 and L3) are only written down for more clearence)

productid parentid assetnumber description
500 00300 PC PD N419 3.0/1G/80Gb CAD (L1)
507 500 00180 Monitor Philips 17 inch LCD 170P6ES (L2)
700 00300 IBM SAN TS DS4000 (L1)
180 700 00120 IBM SAN Enclosure DS4000 EXP710 (L2)
200 700 00110 IBM SAN Enclosure DS4000 EXP710 (L2)
230 180 00140 IBM DISK Fibre 246.8GB/10K (L3)
225 200 00135 IBM DISK Fibre 246.8GB/10K (L3)

The query-result should looks like ....

00300 PC PD N419 3.0/1G/80Gb CAD
00180 -- Monitor Philips 17 inch LCD 170P6ES

00300 IBM SAN TS DS4000
00110 -- IBM SAN Enclosure DS4000 EXP710
00135 ---- IBM DISK Fibre 246.8GB/10K
00120 -- IBM SAN Enclosure DS4000 EXP710
00140 ---- IBM DISK Fibre 246.8GB/10K

THX

Kind Regard
RickZH
Sep 8 '07 #1
Share this Question
Share on Google+
1 Reply


Scott Price
Expert 100+
P: 1,384
You cannot create a one to many relationship within the same table, which is what you are trying to do.

You will have to create a separate table that contains the 'many' products, that is related to the table with the 'one' products.

Example: tblProducts, will contain PCX, tblComponents will contain MonitorX, CPUX, etc.

Regards,
Scott
Sep 8 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.