How to query in MySQL between 3 MTM tables-CodePudding

I'm trying to create a product menu on a website where products can have different sizes and prices. I think I've got the tables in 3FN where there's a table for product names, one for product sizes, and one for product prices. The reason for this separation (as best as I can understand) is that each product will have different sizes and prices, but some products will have the same price when those products' sizes are different. To link these, I've created 3 MTM tables - products and sizes, products and prices, and prices and sizes. What I'm trying to do now is query all of the tables at once so that each product-size-price combo is retrieved correctly so that I can then pass that into the PHP page and display the results as part of the menu. Below you can see where I've partly managed to do so using two tables, but I need to join the information from all 3.

Names-Sizes

Relevant code:

SELECT P.product_name, S.product_size
FROM ebdb.Products AS P
    INNER JOIN ebdb.Prod_Size_Combo AS PS
        ON P.product_id = PS.product_id
    INNER JOIN ebdb.Product_Sizes AS S
        ON PS.size_id = S.size_id
ORDER BY P.product_id ASC, S.size_id ASC

Names-Prices

Relevant code:

SELECT P.product_name, I.product_price
FROM ebdb.Product_Prices AS I
    INNER JOIN ebdb.Prod_Price_Combo AS PP
        ON I.price_id = PP.price_id
    INNER JOIN ebdb.Products AS P
        ON P.product_id = PP.product_id
ORDER BY P.product_id ASC, I.price_id ASC

Prices-Sizes

Relevant code:

SELECT I.product_price, S.product_size
FROM ebdb.Product_Sizes AS S
    INNER JOIN ebdb.Price_Size_Combo AS SI
        ON S.size_id = SI.size_id
    INNER JOIN ebdb.Product_Prices AS I
        ON I.price_id = SI.price_id
ORDER BY S.size_id ASC, I.price_id ASC

What kind of queries can I run to get all three columns but such that the information is correctly being retrieved? I should only have 12 columns total.

CodePudding user response：

After consulting with my team, I found out that you can have a multi-MTM table, from which you can pull all your relevant data simultaneously. See the below model for reference:

Multi-MTM Table model

To pull relevant information from this table, the command would be something like:

SELECT P.product_name, S.product_size, I.product_price
FROM ebdb.PSI_Combo AS C
    INNER JOIN ebdb.Products AS P
        ON P.product_id = C.product_id
    INNER JOIN ebdb.Product_Prices AS I
        ON I.price_id = C.price_id
    INNER JOIN ebdb.Product_Sizes AS S
        ON S.size_id = C.size_id

The resulting information would be displayed as: Table Results

You could search for a specific entry by adding a WHERE clause or WHERE/AND clauses.