CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Evik James
Evik James

Reputation: 10503

How to determine whether a value exists in a junction table and return zero or one?

I am using SQL Server 2008 R2

I am trying to write a single query that will return only exactly what I need. I will drop in a MovieID and get back a list of ALL genres. If the movie represents a specific genre (has an associated record in the junction table), the Checked value will be 1. If not, then 0.

My result set should look like this:

GenreID    Genre    Checked
1          ABC      0
2          DEF      1
3          HIJ      0
4          KLM      1

My First table is named Genres. It looks like this:

GenreID    Genre
1          ABC
2          DEF
3          HIJ
4          KLM

My second table is named Movies. It looks like this:

MovieID   Title
1         Blah
2         Foo
3         Carpe
4         Diem

My third table is a junction table named Movies_Genres. It looks like this:

MovieID   GenreID
1         2
1         1
1         4
2         1
2         3
3         4
4         1

I would normally, do a couple of queries and a couple of loops to handle this, but I want to really just make the database do the work here. How do I tweak my query so that I can get the resultset that I need with just a single query?

Here's the starting query:

SELECT  GenreID, 
        Genre
FROM    Genres

Thanks in advance for your help!!!

Upvotes: 2

Views: 205

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 181037

Pretty straight forward using CASE;

SELECT DISTINCT g.GenreID, g.Genre, 
  CASE WHEN mg.MovieID IS NULL THEN 0 ELSE 1 END Checked
FROM Genres g 
LEFT JOIN Movies_Genres mg
  ON g.GenreID=mg.GenreID 
    AND mg.MovieId=@MovieID;

Demo here.

Edit: If entries are guaranteed to be unique in Movies_Genres, you could choose to drop the DISTINCT.

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280490

SELECT g.GenreID, g.Genre, Checked = CASE WHEN EXISTS
      (SELECT 1 FROM dbo.Movies_Genres AS mg
         INNER JOIN dbo.Movies AS m
         ON mg.MovieID = m.MovieID
         WHERE mg.GenreID = g.GenreID
         AND m.MovieID = @MovieID) THEN 1 ELSE 0 END
FROM dbo.Genres AS g
ORDER BY g.GenreID;

If there is a unique constraint or primary key on dbo.Movies_Genres(MovieID, GenreID) then this can be simply:

SELECT g.GenreID, g.Genre, Checked = COUNT(mg.GenreID)
    FROM dbo.Genres AS g
    LEFT OUTER JOIN dbo.Movies_Genres AS mg
    ON g.GenreID = mg.GenreID
    AND mg.MovieID = @MovieID
    GROUP BY g.GenreID, g.Genre;

...since the count for any genre can only be 0 or 1 given a single @MovieID.

Upvotes: 3

Oleg Grishko
Oleg Grishko

Reputation: 4281

The @MovieID is the movie, you want to filter by.

SELECT  Genres.GenreID, 
        Genres.Genre,
        CASE WHEN (Movies_Genres.GenreID IS NULL)
            THEN 0
            ELSE 1 
        END AS Checked
FROM    Genres LEFT JOIN
        Movies_Genres ON Movies_Genres.GenreID = Genres.GenreID AND
                        MovieID = @MovieID

Upvotes: 1

Related Questions