Return 1 row of data from 3 linked tables

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)

Return 1 row of data from 3 linked tables

Author

Topic

Whamoduck
Starting Member

3 Posts

Posted - 2004-09-24 : 11:26:44

First - Thanks for taking the time to look at my question and offering asistance.

Issue:
One company can have multiple categoryID's and I need to display them on one line as Industry, Category and Sub category. There is a relation ship that is based on the ParentID and CategoryID from Table C you will be able to see when you view the example data.

I have outlined the data below to try and give you a picture and have included my sql code at the bottom that returns 3 rows for company 12345 and returns 2 rows for company 67890.

I need the data to display as follows:

CompanyId - CompanyName - Industry - Category - SubCategory
12345 - ABCDE - Automotive - Auto Repair - All Repairs
67890 - VWXYZ - Home Serice - Landscape

Table A: company
Fields: CompanyId, CompanyName;

Table B: category
Fields: CompanyId, CategoryId

Table C: ref_category
Fields: CategoryId, ParentId, Name

Table A company data
CompanyId CompanyName
12345 ABCDE
67890 VWXYZ

Table B category data
CompanyId CategoryId
12345 1 (This is the industry level)
12345 1002 (This is the category level)
12345 2330 (This is the sub category level)
67890 7 (Industry level)
67890 1063 (Category level)

Table C ref_category data
CategoryId ParentId Name
1 0 Automotive
1002 1 Auto Repair
2001 1002 All Repairs
7 0 Home Services
1063 7 Landscape

The SQL I have so far that does not return 1 record for each companyid.

SELECT comp.Name, cat.companyID, cat.CategoryID, ref_cat.categoryID, ref_cat.parentID, ref_cat.name
FROM company as comp
join category as cat on cat.companyid = comp.companyid
join ref_category as ref_cat on ref_cat.categoryId = cat.categoryID
Where comp.companyID in (12345, 67890)
Order by comp.CompanyId

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-24 : 12:05:02

read up on GROUP BY in books-on line if you need to learn about how to summarize your data.

- Jeff

Whamoduck
Starting Member

3 Posts

Posted - 2004-09-24 : 12:09:37

I am thinking this is going to have more to do with alias tables than grouping. Could you explain how you think group by would solve this issue.

quote:
Originally posted by jsmith8858

read up on GROUP BY in books-on line if you need to learn about how to summarize your data.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources