CODEWITHSUNDEEP
mysqljoininner-join
Arun
Arun

Reputation: 3721

MySQL join multiple tables and limit the output from "ON"

when i write a MySQL query, there occur a problem. here is my query

SELECT 
    SUM(view_product_count_details.view_product_count) AS count_sum,
    product_details.product_name,
    product_details.product_url,
    product_details.product_price,
    product_image_details.product_image_name,
    main_category_details.main_category_url,
    sub_category_details.sub_category_url
FROM
    view_product_count_details
        JOIN
    product_details ON view_product_count_details.product_id_fk = product_details.product_id
        JOIN
    product_image_details ON product_image_details.product_id_fk = view_product_count_details.product_id_fk
        JOIN
    main_category_details ON product_details.product_main_cat_id = main_category_details.main_category_id
        JOIN
    sub_category_details ON product_details.product_sub_cat_id_fk = sub_category_details.sub_category_id
WHERE
    view_product_count_details.view_product_status = 'active'
GROUP BY view_product_count_details.product_id_fk
ORDER BY count_sum DESC
LIMIT 4

Here I have multiple images for one product.the images are in table "product_image_details". this query returns count as the number of images, where I need the count of product viewed by people which is stored in table "view_product_count_details". when I just pick the count, i got the count as it is. but when i join the table "product_image details", result become wrong. Is there any way to do it in single query?

Please help me... Thanks in advance.... :)

Upvotes: 0

Views: 69

Answers (1)

Nish
Nish

Reputation: 1137

You can do it by having an inline query. I am not sure how this will perform when you have more data.

    SELECT table1.*,product_image_details.product_image_name FROM
    (
        SELECT 
            SUM(view_product_count_details.view_product_count) AS count_sum,
            product_details.product_id,
            product_details.product_name,
            product_details.product_url,
            product_details.product_price,
            main_category_details.main_category_url,
            sub_category_details.sub_category_url
        FROM
            view_product_count_details
                JOIN
            product_details ON view_product_count_details.product_id_fk = product_details.product_id
                JOIN
            product_image_details ON product_image_details.product_id_fk = view_product_count_details.product_id_fk
                JOIN
            main_category_details ON product_details.product_main_cat_id = main_category_details.main_category_id
                JOIN
            sub_category_details ON product_details.product_sub_cat_id_fk = sub_category_details.sub_category_id
        WHERE
            view_product_count_details.view_product_status = 'active'
        GROUP BY view_product_count_details.product_id_fk
        ORDER BY count_sum DESC
        LIMIT 4
    ) table1
        JOIN
    product_image_details ON product_image_details.product_id_fk = table1.product_id    
    LIMIT 4

Upvotes: 1

Related Questions