I have a
main table set up like this:
I have three other tables called
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