Query... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query...

Author

Topic

Mazdak
Yak Posting Veteran

63 Posts

Posted - 2003-02-22 : 13:56:08

I have some tables:

Table1:ORDERS with these columns:
1.OrderID(PrinaryKey)
2.CategoryName
3.ItemID

Table2:CATEGORY1 with these columns:
1.ID(PrimaryKey)
2.ItemName

Table3:CATEGORY3 with these columns:
1.ID(PrimaryKey)
2.ItemName

....CATEGORY4...CATEGORY5....with the same design.
ItemID in ORDERS table is ID column in each CATEGORY table ,now I need the to query like this:
Select from ORDERS table and for each CategoryName and ItemID in it get its ItemName in its CATEGORY.

Bambola
Posting Yak Master

103 Posts

Posted - 2003-02-22 : 15:44:18

If I understood you well, categoryName in table ORDERS represents the table name in which the item is. If so, I cannot see how it is possible to get the data you want. (ok, it's possible but it doesn't see like a good idea :-)

I can suggest the following structure:

CREATE TABLE ORDERS
(
OrderID int IDENTITY primary key
,CategoryID int -- FK
,ItemID int -- FK
)
CREATE table CATEGORY
(
CategoryID int IDENTITY Primary Key
--(or you could use char/varchar for something more understandtable)
,CategoryName varchar(20)
)
CREATE table ITEMS
(
ItemID int IDENTITY Primary Key
,CategoryID int -- FK
,ItemName varchar(20)
)

-- putting some fake data in tables.

INSERT INTO CATEGORY select 'CATEGORY1'
INSERT INTO CATEGORY select 'CATEGORY2'
INSERT INTO CATEGORY select 'CATEGORY3'

INSERT INTO ITEMS select 1, 'Item1'
INSERT INTO ITEMS select 1, 'Item2'
INSERT INTO ITEMS select 3, 'Item3'
INSERT INTO ITEMS select 3, 'Item4'

INSERT INTO ORDERS select 1,1
INSERT INTO ORDERS select 2,1

To get the data you want, you would have to a query joining this
3 table.

SELECT o.OrderID, c.CategoryName, i.ItemName
FROM ORDERS O
INNER JOIN CATEGORY c ON o.CategoryID = c.CategoryID
INNER JOIN ITEMS i ON i.ItemID = o.ItemID

This structure will also allow you more flexability. you would be able to associate one item to more then one category, to freely update category/item name, etc.

HTH,

Bambola.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources