Am I SOL? - 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)

Am I SOL?

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2001-06-09 : 13:26:44

I have two tables, and one table that matches them with a many - many relationship. The tables:

categories:
catId
catName

categories_subTypes:
subCatId
subCatName

categories_matches:
catId
subCatId

I am having trouble with one particular query. What I need is the full list of sub-categories, without duplicate subCatNames. Simple you say, correct? Well here's the twist. If there is a duplicate, then I need to return the one that corresponds to the category that is currently being viewed on the page if it exists. If not, then I need to return the sub category anyways.

I have this worked out with a time wasting ASP script, but I would much rather have the SQL do the work for me.

Here is what I'm using right now. It returns all of the subcategories, but returns duplicates.

SELECT
CS.subCatName,
CS.subCatId,
CM.catId AS catMatch

FROM categories_matches CM
RIGHT JOIN categories_subTypes CS ON CM.subCatId = CS.subCatId
ORDER BY subCatName

Here's a sample of the results:

Aprilia 1 1
Bimota 2 1
blhabhl 41 NULL
BMW 3 1
Brackets & Bars 4 4
Buell 6 1
Bultaco 7 1
CCM 9 1
Ducati 10 1
Fairings 11 3
Foam 12 4
Fuel 13 4
Harley - Davidson 15 1
Harley - Davidson 15 2
Honda 17 1
Kawasaki 18 1
Kawasaki 18 2
Laverda SFC 19 1

So in this instance if I were looking in category 1, then I would want the only the first Harley - Davidson, first Kawasaki, and the remaining records.

Edited by - blastrix on 06/09/2001 13:32:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources