table design question - 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

Other Forums

MS Access

table design question

Author

Topic

talya
Starting Member

2 Posts

Posted - 2002-08-08 : 23:54:22

hello I am new to databases. I have db, that holds two tables, one for suppliers and one for industries. They are joined together via the supplierID field. The industry table columns are industry names and each row represents by a 1 or checkmark, which industry each supplier is in.
I am trying to perform a query that can give a list of company name's that the user can search via state and industrytype. Meaning "search for all suppliers in new york that are the metals industry. I can't seem to figure out how to build the query since all the industry names are the column names, I am pretty sure that I would have to redesign the industy table. Can someone please point me in the right direction?
Thanks in advance!

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-08-09 : 01:48:58

quote:

hello I am new to databases. I have db, that holds two tables, one for suppliers and one for industries. They are joined together via the supplierID field. The industry table columns are industry names and each row represents by a 1 or checkmark, which industry each supplier is in.
I am trying to perform a query that can give a list of company name's that the user can search via state and industrytype. Meaning "search for all suppliers in new york that are the metals industry. I can't seem to figure out how to build the query since all the industry names are the column names, I am pretty sure that I would have to redesign the industy table. Can someone please point me in the right direction?
Thanks in advance!

No offense, but you didn't do yourself any favors with your current design. If you want to make your life easier in the long run hunker down for a redesign.

No sense in me regurjitating what somebody much more accomplished than I already answered [url]http://www.intelligententerprise.com/010101/celko.shtml[/url]

hth,
Justin

tad
Starting Member

31 Posts

Posted - 2002-08-15 : 10:10:18

I would suggest three tables with Supplier and Industry being Domain tables. The third table would be an associative table. This solution would allow a supplier to particapate in multiple industries and you can add industries without having to alter your table.

Your query would be:

SELECT SUPPLIER.*
FROM INDUSTRY
INNER JOIN
ON IndustryParticipants.IndustryID = Industry.IndustryID
INNER JOIN SUPPLIER
ON Supplier.SupplierID = IndustryParticipants.SupplierID
WHERE Industry.iType = 'Metals'
AND Supplier.sState = 'NY'

[SUPPLIER]
SupplierID (AutoNumber)
sName
sAddress
sState
sZip

[INDUSTRY]
IndustryID (AutoNumber)
iName
iType

[INDUSTRYPARTICIPANTS]
IndustryParticipantsID (AutoNumber)
SupplierID
IndustryID

quote:

hello I am new to databases. I have db, that holds two tables, one for suppliers and one for industries. They are joined together via the supplierID field. The industry table columns are industry names and each row represents by a 1 or checkmark, which industry each supplier is in.
I am trying to perform a query that can give a list of company name's that the user can search via state and industrytype. Meaning "search for all suppliers in new york that are the metals industry. I can't seem to figure out how to build the query since all the industry names are the column names, I am pretty sure that I would have to redesign the industy table. Can someone please point me in the right direction?
Thanks in advance!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources