MySQL Multiple select

Question

I have an indextable which hold a content_id and type colomn. (eg. content_id=1, type=audio. content_id=1, type=video) Would it be possible to get the right data based upon content_id from the right tables (tables: contentvideo, contentaudio)?

Answer 1

Have you tried CASE, or IF?

http://dev.mysql.com/doc/refman/6.0/en/case-statement.html

Answer 2

I take it you want to retrieve the content via a single select statement? If the layout of your content tables is exactly the same you can use UNION:

SELECT ca.* FROM contentaudio ca, indextable it
 WHERE ca.content_id = it.content_id
   AND it.content_type = 'audio'
UNION
SELECT cv.* FROM contentvideo cv, indextable it
 WHERE cv.content_id = it.content_id
   AND it.content_type = 'video'
ORDER BY content_id /* or whatever */

Note that if content_id is not unique across all of your content tables, you may get multiple records (of different types) for the same content_id. Also note that if you're selecting a particular record based on some indextable key, you'll need to add it to each part of the union.

If your content tables have different layouts, you can use an OUTER JOIN instead and retrieve your various content types as part of one record (missing ones will be returned as NULLs):

SELECT * FROM indextable it
  LEFT OUTER JOIN contentaudio ca ON it.content_id = ca.content_id
  LEFT OUTER JOIN contentvideo cv ON it.content_id = cv.content_id
 WHERE it.some_key = ?

Note that content_type is not involved in this case at all (nor do you really need it for UNION select either).