SQL Statement for List...

[greatcanada]

Hello everyone,

I have an SQL question that for the life of me, I cannot seem to write it to do what I want it to do.

Here's a background:

1. There are 2 tables (company & industry)

2. The company table has a field (which is a comma delimited list of industry ID's) that the company belongs to.

3. And the industry table just contains an Industry's ID

Example:

Company table:-----------

companyID, companyName, ... CompanyIndustries, ...
1, Microsoft,....,'1,3,5,8', ....
2, Vodaphone,...., '3,4,8,12,15', .....
3, Ford, ...., '2,8,13,15',....

etc.

- CompanyID is an autoid for the record.
- CompanyName is the name of the company.
- CompanyIndustries is the field that contains the comma-delimited list of industries the comapny belongs to.


Industry table: ----------

industryID, industryName, ....
1, Agriculture
2, Automotive
3, Computing
4, Consumer Goods
5, Electronics
...
13, Manufacturing
14, Metals & Mining
15, Natural Resource

etc.



My SQL questions then are:

1. What SQL statement would I use to show the name of companies & its associated industryname that have, say an industryID of 3?

2. Alternatively, what SQL statement would I use to list the name of companies & its associated industryname that have specific industry (like 3,5,7,9)?


3. What SQL statement would I use to display ALL the companies in the company table SORTED BY INDUSTRYNAME?


I am open to any suggestion. Perhaps its the way that I'm recording the industry that the company belongs in that makes my querying difficult.

Would it be better if I created another table to record each industry that the company belongs to instead?

To use the above as an Example, would this be better?:

CompanyLog table: -----------------

CompanyID, IndustryID
1, 2
1, 3
1, 5
1, 8
2, 3
2, 4
2, 8
2, 12
2, 15
3, 2
3, 8
3, 13
3, 15
etc.....

Thank you!

Christopher

 

[GunJack]

Hey Christopher,

Although I think you do most of what you want with the current structure, I think you'll find things much easier if you structure it the way you suggested with a single record for each company to industry relation. In this case, question 1 would be something like this:

select company.companyName, industry.Industryname from company,industry,companyLog

where company.companyID = companyLog.companyID
and compnayLog.industryID = industry.industryID
and industry.industryID = 3

question 2:

select company.companyName, industry.Industryname from company,industry,companyLog

where company.companyID = companyLog.companyID
and compnayLog.industryID = industry.industryID
and industry.industryID in (3,5,7,9)

question 3:

select company.companyName, industry.Industryname from company,industry,companyLog

where company.companyID = companyLog.companyID
and compnayLog.industryID = industry.industryID

order by industry.industryName asc, company.companyName asc

If you're using MS Access, you'll have to modify these joins to use the "inner join" syntax but this should work for Sql Server and other mainstream Dbs.

Hope this helps,
GJ