Thomas’ Developer Blog

February 4, 2008

SQL Many-to-Many Queries: Exact Matches

Filed under: Asp.Net Framework 2.0, sql, vb.net — Tags: , , , , , , , , , , , , — sanzon @ 2:56 am

So ever run into that situation where you have to do an EXACT match for a sql query? Let’s say for example you have an online store and in that store and in that store you have a search system. Well you might for example have a database setup with three tables (yeah… if only I could make a 3 table database). Those tables would be…. Video, Genre, and Video2Genre. Just to nag a bit but NEVER use plural names for tables. I myself prefer using Table2Table for M2M since it just comes out a lot cleaner.

Well back to the example. What if the person really likes horror flicks, but just happens to be in the mode for a romance flick as well. Romanic Horror, I’m sure we all have had it at some point in our lives. Well the problem is doing an exact match for Romance and Horror. With a M2M table you might get something like this….

Video_ID Genre_ID
1 1
1 2
1 4
2 2
2 6
2 9
3 1
3 2
4 3

Well with the code above it obviously gets a lot more complicated the more you add. The problem is how do you select multiple genre for one title?

You could try:
SELECT * FROM video2genre WHERE video2genre.Genre_ID = 1 AND video2genre.Genre_ID = 4/code>Sadly the above code won't return any results. Because SQL reads the entries per row and not based on Video_ID. To get around this we use subqueries. A subquery is simply a query within a query.Example:
SELECT * FROM video2genre WHERE video2genre.Video_ID IN (SELECT * FROM video2genre WHERE video2genre.Genre_ID = 1)

What this code does is select any video title that has a genre ID of 1 without having to use a JOIN statement. The best part of doing this is you can do it more then once. What this does is it checks the Video_ID and NOT THE ROW. So now that we can check the video_id instead of the row... we simply use an AND statement.

SELECT * FROM video2genre WHERE video2genre.Video_ID IN (SELECT * FROM video2genre WHERE video2genre.Genre_ID = 1) AND video2genre.Video_ID IN (SELECT * FROM video2genre WHERE video2genre.Genre_ID = 4)

And there we got it! It will only bring up Video_ID 1! Now of course this can become really complex typing this all out. So we need to use a loop statement to automatically add to the statement!


  Dim iStr As Integer = Request.QueryString("g").Split(New [Char]() {","c}).Length
  For Each s As String In Request.QueryString("g").Split(New [Char]() {","c})
  If s.Trim() <> "" Then
Select Case Request.QueryString("go")
  Case "1"
  adoCmdStrWhere += "video2genre.genre_id = ? "
  Case "2"
  adoCmdStrWhere += "video2genre.video_id IN (SELECT video2genre.video_id from video2genre where video2genre.genre_id = ? ) "
  Case "3"
  adoCmdStrWhere += "video2genre.genre_id = ? "
  Case Else
  adoCmdStrWhere += "video2genre.genre_id = ? "
  End Select
iStr = iStr - 1

adoParamQueue.Enqueue("Int")
  adoParamQueue.Enqueue("4")
  adoParamQueue.Enqueue(s)

If iStr <> 0 Then
  Select Case Request.QueryString("go")
  Case "1"
  adoCmdStrWhere += " OR "
  Case "2"
  adoCmdStrWhere += " AND "
  Case "3"
  adoCmdStrWhere += " OR "
  Case Else
  adoCmdStrWhere += " OR "
  End Select
  End if
  End If
  Next s

If request.querystring("g").indexof(",") > -1 Then
  adoCmdStrWhere += " )"
  End If

If request.querystring("go") = "3" OR request.querystring("go") = "1" Then
  adoCmdStrWhere += ")"
  End If
  End if

I'm not going in depth on the code above! Basically all it does is take the value from a querystring for genre! In this case it has multiple cases so... it might go something like:

?g=1&g=4&go=2

What the code does is goes ahead and splits up the genre and in VB.Net saves it as g=1,4 so we use a split statement to spit it up using , as a delimitor. Once it's split up we use a loop to scroll through and tag onto the comand string that will send the code to your SQL client to search for an EXACT match.

Yes the code is really long and may look something like….


Select distinct video.Video_ID, video.Title , video.Synopsis, video.ContentRating_ID, video.Premiere, Video.Conclusion, contentrating.Name as ContentRating from video left join video2genre on video.Video_ID = video2genre.Video_ID left join contentrating on video.ContentRating_ID = contentrating.ContentRating_ID WHERE (video2genre.video_id IN (SELECT video2genre.video_id from video2genre where video2genre.genre_id = ? ) AND video2genre.video_id IN (SELECT video2genre.video_id from video2genre where video2genre.genre_id = ? ) AND video2genre.video_id IN (SELECT video2genre.video_id from video2genre where video2genre.genre_id = ? ) ) AND contentrating.AgeDemographicFrom <= ? ORDER BY Video.Title ASC, Video.Title ASC

Of course I’m using an ODBC client so I have ? setup as a paramater. View my previous post to learn more about the queue statement above and how you can use it to store your paramaters until they are ready to be accessed.

Advertisements

2 Comments »

  1. Thanks, this helped me out in a jam

    Comment by Winston — September 25, 2008 @ 10:21 am

  2. Thanks, it helped me, by solving same kind of problem.

    Comment by max — January 17, 2009 @ 11:34 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: