Jump to content
LCVG

SQL JOIN help


foogledricks
 Share

Recommended Posts

I have a basic blog-esque database. Three main tables:

  • Stories(id,title,description)
  • Photos(id)
  • Videos(id)

Now, I wanted to link the stories table to photos and videos and I wanted to be able to associate a story with more than one video and photo. So I created an extra table to keep track of that. So here is my final list of tables.

  • Stories (id,title,description)
  • PhotosPlaylist (id,storyID,photoID)
  • Photos (id)
  • VideosPlaylist (id,storyID,photoID)
  • Videos (id)

Sow how do I build the SELECT statement to give me a list of all the photos and videos who are linked to a story? This only gets me a list of stories that contain both a video and photo (not the whole list):

 

SELECT stories.title,album.id,photo.id

FROM stories

INNER JOIN PhotosPlaylist ON PhotosPlaylist.storyID=PhotosPlaylist .id

INNER JOIN Photos ON PhotosPlaylist.photoID=Photos.id

INNER JOIN VideosPlaylist ON VideosPlaylist.storyID=VideosPlaylist .id

INNER JOIN VideosON VideosPlaylist.photoID=Videos.id

 

It's like I need an OR between the first two JOINS and the second ones. Infact, I could create two different SELECT statements that split them up and it solves my problem. But I don't want to have to do two statements if I don't have to.

 

Hmm, now I'm thinking there probably is no benefit to doing the video and photos stuff together is there? It doesn't save me time and I'm still hitting the same tables the same number of times when you add it all up I think.

 

Thoughts?

Link to comment
Share on other sites

Shouldn't those be:

INNER JOIN PhotosPlaylist ON PhotosPlaylist.storyID=Stories.id

INNER JOIN VideosPlaylist ON VideosPlaylist.storyID=Stories.id

?

 

The problem is that INNER JOIN requires results for each side of the JOIN and is transitive, which is why you're only getting results that have both photos and video. I think you could get away with (might have to play with parentheses placement):

 

SELECT stories.title,album.id,photo.id

FROM stories

RIGHT OUTER JOIN (PhotosPlaylist ON PhotosPlaylist.storyID=Stories.id

INNER JOIN Photos ON PhotosPlaylist.photoID=Photos.id)

RIGHT OUTER JOIN (VideosPlaylist ON VideosPlaylist.storyID=Stories.id

INNER JOIN Videos ON VideosPlaylist.photoID=Videos.id)

WHERE Stories.id IS NOT NULL

Link to comment
Share on other sites

Shouldn't those be:

INNER JOIN PhotosPlaylist ON PhotosPlaylist.storyID=Stories.id

INNER JOIN VideosPlaylist ON VideosPlaylist.storyID=Stories.id

?

I approximated my problem through a simpler example that I typed up on the fly... so that was just a typo.

 

I'll try out your approach. Thank you for the suggestion.

Link to comment
Share on other sites

Why do you need a separate Photo and Video play list? That makes things more complicated. You can easily get away with just a table that has the following:

 

PlayList (storyID, photoID, videoID)

 

You can use all 3 columns to be the primary key to ensure uniqueness. Then you will be able to select either just the photos, videos or both for a story. However, this might not be what you are looking for. :)

Link to comment
Share on other sites

Well, that sort of negates my purpose for having a playlist at all I think. I might as well just put those two videoid/photoid fields directly into my stories table in that case. What I wanted to do was to have as many photo albums and videos as I wanted per story. Doing it the way you suggest implies that I either have a video, photo, or both, but not more than one of either. Right?

Link to comment
Share on other sites

I would advise you to have a single table called "playlists", with a media_type column that could be either photo or video, that can then be selected from as you see fit without the need for different tables, but I can't, because my cognitive ability, when combined with Joey's, is only equivalent to that of a retarded chipmunk.

 

 

Carlos.

Link to comment
Share on other sites

Well, that sort of negates my purpose for having a playlist at all I think. I might as well just put those two videoid/photoid fields directly into my stories table in that case. What I wanted to do was to have as many photo albums and videos as I wanted per story. Doing it the way you suggest implies that I either have a video, photo, or both, but not more than one of either. Right?

 

Not at all, you can have as many photos and videos as you want as all 3 fields are primary keys. The storyID can be repeated over and over and will have multiple photos or videos assigned to it in the table. Then when you do a search, you can just search via the storyID. For example:

 

storyId, photoID, videoID

1,0,1

1,1,0

1,2,0

1,0,2

1,0,3

2,3,0

2,0,1

1,3,0

 

This way, you can easily select photos and videos based on the storyID, or just photos or videos. I would definitely concentrate more on building more efficient tables using an ER Diagram. Doing multiple joins is quite extensive and the larger the tables get the slower the performance is.

Link to comment
Share on other sites

Thanks for the advice guys. I am more and more impressed with the advances and achievement of the retarded chipmunk crowd. Gold star.

 

Romar, I am aware of course that I could do a SELECT and get all the data from that one table the way you suggested. I just thought that configuring the table in that way was inefficient since every row would have a video and photo field and one or the other would go unused frequently in that configuration.

 

However, as you point out doing multiple table joins is even more inefficient. I'm still very new to this stuff so I appreciate these suggestions bigtime.

 

Romar, do you think that Carlucci's suggestion of having a media type is better or worse than what you suggested? It seems that you would have the same or fewer rows with your suggestion. Though Carlucci's suggestion makes it a bit easier to read the SQL statements I think.

Link to comment
Share on other sites

Assuming your photos and videos have the same meta-data, put them in one media table.

 

Assuming each photo/video is associated with only one story, you could just put the story id in the media table. You only need those intermediary tables to model a many-to-many relationship.

 

So you'd have:

Story (id, title, etc...)

Media(id, story_id, type, title, etc...)

 

Since you're not going to want to repeat the story information for every media item associated with it, I'd just go with two queries.

One to get the story info, and one to get all media entrys where Media.story_id = (the story id)...

Link to comment
Share on other sites

Yeah, a media table would be ideal too as Carlucci and Ron suggested. It's easier on the eyes but technically will take the same number of rows as what I laid out as you will have to have a column that specifies which type it is. In the table I specified, you can have nulls for the photoID and videoID columns and that might actually save space. Once indexed, it can be quick to do a search on a straightforward table. But either way will give you the same result and it's up to what you prefer.

 

Sometimes a picture might be useful in more than one story so I wouldn't limit the choices with that. :D I actually had a very nice Wordpress based web site with various stories on sites within downtown Toronto. I did a lot of customization and was able to create a google map with markers of all those sites intertwined with my stories and photo albums. Unfortunately my web host disappeared and I haven't had the time to rebuild it all. :(

Link to comment
Share on other sites

Trying not to be the obligatory Rails hipster?, but honestly using an object-relational mapping framework like ActiveRecord makes your life so much easier. SQL is a hideous language, and not only that, using it inline in the usual way requires you to switch mentally and programmatically between relational and object-oriented models of your data.

Link to comment
Share on other sites

Using the .Net frame work it translates everything into objects making them easier to work with. Specifically LINQ. Which I haven't used yet. So I don't know what I'm talking about. But I really want to get to using it.

 

I'm using ASP.net and SQL Server, so I am bound to those platforms as it is what I know, the .net framework and VB.net specifically.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...