can thsi be done with a join?

[jonniejoejonson]

featured_media table

image table

video table

sound table

 

I want to select 10 records from the featured_media table and then get that medias details from either the image / video / sound table, depending on the media_type.

 

The featured_media table has a media_id and media_type columns so that i can get the selected media, however at the moment i am having to do, 

one query to get the 10 featured media records.

then 10 queries to get the relevant media details.

 

if the image / video/ sound tables were one table you could do the whole thing with one sql query...

 

any ideas regards J.

[ajlisowski]

yes, you could get all the data you needed with a join.

 

SELECT f.`media_type`, i.`file` AS `image`, v.`file` AS ` `video`, s.`file` AS `sound` FROM

`featured_media_table` AS `f`

LEFT JOIN `image_table` AS `i` ON (f.`media_id`=i.`media_id` AND f.`media_type`=1)

LEFT JOIN `video_table` AS `v` ON (f.`media_id`=v.`media_id` AND f.`media_type`=2)

LEFT JOIN `sound_table` AS `s` ON (f.`media_id`=s.`media_id` AND f.`media_type`=3)

 

however...i dont see a reason to have the video, image and sound files seperate like that. Do the tables contain other information that is unique to a file type? Like captions and stuff?

 

if so, id set it up as follows:

 

file_table (contains id, file location and type as well as any other info shared by all 3 types)

image_table (contains id and any info unique to the image)

video_table (contains id and any info unique to the video)

sound_table (contains id and any info unique to the sound)

 

that way when you are just grabbing basic shared info you wont need to mess with un-needed joins.

 

If, however, video, image and sound tables dont have any unqiue fields, you might as well combine them to just have the one table.

[jonniejoejonson]

thanks ajlisowski. I will take a look at that.

Unfortuanelty I have already developed the site with these seperate tables, so it is a bit late now change the whole structure...

however for the future... a site like facebook for example, would they have a seperate tables for images, videos and sounds like i have... or would they simply have one large table of 'media' and then sperate tables for the uncommon fields? regards J.

[ajlisowski]

it would depend on whether or not there is a need for those tables to be split. Basically, if all your media tables are going to contain the same fields, theres no need to have multiple. If they are going to have their own individual fields, (like caption for an image, or description for a video) then seperate tables would make sense.

 

Another thing to consider is how you plan on using the data. In your case, you plan on querying all 3 formats at once. If this wasnt the case, and a majority of the queries were going to be just for a specific type, it may make sense to keep them seperate to keep the table size down by about a third when you query them.

[jonniejoejonson]

Thanks Alijowski,

well in the case of a social network then i imagine that it best to seperate, seeing as the smaller the tbale size the better... and it is only on a few pages that require the searching of all three tables at once... thanks for your help...

kind regards J