CODEWITHSUNDEEP
sqlsql-server-2008razor
kazinix
kazinix

Reputation: 30093

SQL - Select distinct row with multiple categories

I have three database tables:

Story

Category

StoryCategory

Note: Story and Category has a many-to-many relationship (a story can have multiple categories and a category can have multiple stories) so I created the StoryCategory. Also, a story can have no category.

I also have a web page (I'm using razor):

<form action="" method="get">
    <label for="keyword">Keyword:</label>
    <input name="keyword" type="text" value="@Page.Keyword" />
    <label for="category">Category:</label>
    <select name="category">
        <option value="">All</option>
    @foreach(var category in Page.Categories)
    {
        <option [email protected] @(category.Id == Page.Category? "selected=selected" : "")>@category.Title</option>
    }
    </select>
    <input type="submit" value="Search" />
</form>

To make it simple, this page allows the user to enter a keyword to search for specific story and it also allows the user to select the category which the story belongs to.

I can't find a way to find the stories attached to a specific category. Here's my first code (keyword disregarded to focus on category):

select s.title --columns will be added here such as name of category/ies, author etc.
from story as s
left join --left join so the uncategorized stories will not be excluded
storyCategory as sc
on s.id = sc.storyId
where sc.categoryId = @selectedCategory --this line is removed when there is no category selected

Sample Data:

The problem is, if there is no selected category and if story has multiple categories, it will appear also multiple times:

naruto (fantasy)
naruto (action)
bleach (fantasy)

I actually know what is happening but I can't think of a best solution to solve the problem.

Upvotes: 2

Views: 2461

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21756

Use the DISTINCT keyword and left join is useless with your WHERE condition

select DISTINCT
  s.title --columns will be added here such as name of category/ies, author etc.
from story as s
join storyCategory as sc--left join so the uncategorized stories will not be excluded
on s.id = sc.storyId
where sc.categoryId = @selectedCategory --this line is removed when there is no category selected

If you need the list of categories attached to each story - see and examine the next query. Valid for sql server:

DECLARE @Stories TABLE(Id INT IDENTITY PRIMARY KEY, NAME NVARCHAR(100) NOT NULL) 
DECLARE @Categories TABLE(Id INT IDENTITY PRIMARY KEY, NAME NVARCHAR(100) NOT NULL) 
DECLARE @Fork TABLE(StoryId INT NOT NULL, CategoryId INT NOT NULL, PRIMARY KEY(StoryId, CategoryId)) 

INSERT @Stories 
VALUES ('Story1'), ('Story2'), ('Story3') 

INSERT @Categories 
VALUES ('Category1'), ('Category2'), ('Category3') 

INSERT @Fork 
VALUES(1,1), (1,2), (3,3), (2,3) 

DECLARE @selectedCategory INT = 3 

select 
s.NAME, 
( 
SELECT c.Name + ',' 
FROM @Categories c 
JOIN @Fork f ON f.CategoryId = c.Id AND f.StoryId = s.Id 
ORDER BY c.Name 
FOR XML PATH('') 
) Categories 
from @stories s

Upvotes: 3

Sparky
Sparky

Reputation: 15085

If you are not limiting it to a single category, try something like this:

select s.title,count(*) as NumCategories 
from story as s
left join storyCategory as sc on s.id = sc.storyId
group by s.title

You could also find some categories using something like, which shows the number of categories and the first and last (based on title characters) categories the story is in

select s.title,count(*) as NumCategories,min(sc.title),max(sc.title)
from story as s
left join storyCategory as sc on s.id = sc.storyId
group by s.title

Upvotes: 0

Related Questions