0

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
4
  • Think carefully do you really need in 3 separate slave tables. Maybe one table (maybe even sparse) will be more useful? PS. LIMIT without ORDER BY is strange..
    – Akina
    Aug 28, 2023 at 4:35
  • So, you want to join three tables without using joins, because they seem messy?
    – mustaccio
    Aug 29, 2023 at 15:21
  • A tip: The schema pattern with conditional links to different tables is a nightmare to code and terrible for performance. Re-think.
    – Rick James
    Sep 16, 2023 at 18:32
  • @RickJames - How would you handle this, what do you suggest? I'm interested in learning different strategies from people who know more about this than I do. Thanks
    – peppy
    Sep 16, 2023 at 23:50

1 Answer 1

0

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.

2
  • I'm not sure what the relationship is. The products, images and videos are definitely independent of each other, but are all connected the main table. The main table is sort of like a general news feed where each "post" is something random each day. One day, we might attach a video, another other day we might attach a product, and another day is just attach a random image (not related to any product, just a picture of the weather for example).
    – peppy
    Sep 17, 2023 at 21:37
  • @peppy - I would expect a "product" to have "0 or more" (unlimited number) images and videos. But I don't know if one image/photo can be associated with more than one product. So, I explained both cases. Analyze your data. Search on some of the keywords I have tossed around.
    – Rick James
    Sep 18, 2023 at 0:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.