Help with SQL Query: Is there a cost-efficient way to join multiple tables to one main table?

Question

I have a main table set up like this:

id	table_to_join	table_item_id
1	products	123
2	products	577
3	products	344
4	products	1230
5	images	14
6	images	42
7	video	555
8	products	400
9	video	9
10	images	19

I have three other tables called products, images and videos. Their Primary ids correspond to those of the table_item_id shown in the main table above, which is an indexed column. I would like to query the main table and select those 10 rows, and join those three tables to their corresponding table_item_id to produce one single list showing a variety of different media.

I am looking for an efficient query to do something like this, if it's even possible. I had thought of three Joins, but that seems a bit messy, especially if I start adding more than the three tables in the future. I had also thought of using individual ajax queries on each table after it displays on the homepage, but again that seems a bit messy.

The query below is sort of what I'm looking for, but I'm not sure if there is a better, more cost-efficient method that best fits what I'm intending to do. Let me know what you think.

SELECT * FROM main 
  JOIN products ON main.table_item_id = products.id AND table_to_join = 'products' 
  JOIN images ON main.table_item_id = images.id AND table_to_join = 'images' 
  JOIN video ON main.table_item_id = video.id AND table_to_join = 'video' 
LIMIT 10

Answer 1

Is the "product-to-image" a "one-to-many" relationship? Or "many-to-many"? (The answer determines how to build the schema for that part.) Have 3 "Entity" tables (products, images, videos), plus one table per many-to-many "Relationship".

For 1:many: The column product_id is in the table images.

For many:many:

CREATE TABLE products_images (
    product_id ...,
    image_id ...,
    PRIMARY KEY(product_id, image_id),
    INDEX(image_id, product_id)
) ENGINE=InnoDB;

Note that main is not needed.