not sure how to build this query

Question

Post reply

not sure how to build this query

Blair Dee

SSCommitted

Points: 1648
More actions
March 13, 2007 at 5:52 pm

#95042

I have three tables that are as follows:
tblTitles
ID PK
MovieTitle
MovieDescription
tblGenre
ID PK
Name
tblTitleGenre
ID PK
titleID FK
genreID FK
so the tblTitleGenre table links the tblGenre table and the tblTitles table.
each title has more than one genre that it belongs to.
i woudl like to return a query that contains the movie title, description and a list of the genre names for that movie.
is that possible to do via SQL?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Yes... but this looks like homework... would you mind showing us what you've tried?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Blair Dee SSCommitted Points: 1648 More actions · Answer 2

homework - haven't had that in years.

well for now i am doing the following (note that i simplified the field names in my original post so they are different here):

SELECT tblTitles.*, tblGenres.nvcName as Genre

FROM tblGenres

INNER JOIN (tblTitles INNER JOIN tblTitleGenre ON tblTitles.intID = tblTitleGenre.intTitle)

ON tblGenres.intID = tblTitleGenre.intGenre

ORDER BY tblTitles.nvcLocalTitle

most titles in the database have 3 or 4 genres, so this query returns a row for each genre. so if for instance, "Rocky" is set as Action, Drama and Sports then the results look like this:

Title ... Genre

Rocky ... Action

Rocky ... Drama

Rocky ... Sports

so for now what i am doing is handling this with my ASP code. looping through the results and if the current row is the same as the previous row, just add the genre to the genres variable. if its different, then output the title information and the list of genres.

it works fine that way but i was just wondering if there was a way to handle this using sql and keep that logic out of my ASP code.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

>>just add the genre to the genres variable

Are you looking for the Genres variable to contain a list of space separated Genre's? or ???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Blair Dee SSCommitted Points: 1648 More actions · Answer 4

yes i am looking for the genre variable to contain a comma seperated list of genres for the title. just wondering if it was possible to return that in a recordset or if my only option is to do that as i am now via ASP.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

Here's a fully functional example, using the Northwind database, of how you could do it in SQL Server... most folks turn it into a function....

USE NorthWind

DECLARE @RegionToFind VARCHAR(20)

SET @RegionToFind = 'OR'

DECLARE @CsvCustID VARCHAR(8000)

SELECT @CsvCustID = ISNULL(@CsvCustID+',','') + CustomerID

FROM Customers

SELECT @RegionToFind,@CsvCustID

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)