CODEWITHSUNDEEP
mysqlsql
Sajad
Sajad

Reputation: 3807

Retrieving last record of a table inside inner join

I have these three tables:

products
+----+--------+
| id | QRCode |
+----+--------+
|  1 | 1000   |
|  2 | 1001   |
+----+--------+

prices
+----+---------+------------+
| id | price   | product_id |
+----+---------+------------+
|  2 | $100001 |          1 |
|  3 | $100002 |          1 |
|  4 | $90001  |          2 |
|  5 | $90002  |          2 |
+----+---------+------------+

colors
+----+--------+-------------+
| id | color  | product_id  |
+----+--------+-------------+
|  1 | ffffff |           1 |
|  2 | f2f2f2 |           1 |
|  4 | aaaaaa |           2 |
|  5 | a3a3a3 |           2 |
+----+--------+-------------+

I would like to merge these three in a way that returns:

This is the desired output:

+--------+----------------+-------------+-------------+
| QRCode |    colors      |    price    | product_id  |
+--------+----------------+-------------+-------------+
|  1000  | ffffff, f2f2f2 |   $100002   |      1      |
|  1001  | aaaaaa, a3a3a3 |   $90002    |      2      |
+--------+----------------+-------------+-------------+

Things I tried:

The query below returns product_id of last record of each grouped price

SELECT product_id FROM price where id IN 
                                        (SELECT max(id) FROM price
                                         GROUP BY product_id)

Then I tried to put query above in this query as a subquery

SELECT products.QRCode, priceSubQ.price, GROUP_CONCAT(colors.color) as colors FROM products
INNER JOIN colors on colors.product_id = products.id
INNER JOIN ( /* I put query above here */ ) as priceSubQ ON priceSubQ.product_id = products.id
GROUP BY products.id

What am I doing wrong?

Upvotes: 0

Views: 63

Answers (2)

Sajad
Sajad

Reputation: 3807

I came across this link which helped me understand the problem

Changed inner query to:

SELECT product_id FROM ANY_VALUE(price) where id IN 
                                    (SELECT max(id) FROM price
                                     GROUP BY product_id) group by product_id

solved my problem.

Upvotes: 1

J A
J A

Reputation: 1766

Something like the following should work (not tested)..

SELECT 
    products.QRCode, 
    priceSubQ.price, 
    GROUP_CONCAT(colors.color) as colors 
FROM 
    products
    LEFT JOIN colors 
        ON colors.product_id = products.id
    LEFT JOIN (
        SELECT
            MAX(p1.id) as p1maxId,
            p2.price AS price,
            p2.product_id AS product_id
        FROM
            prices p1
            INNER JOIN prices p2
                ON p1.p1maxId = p2.id
        GROUP BY
            p1.product_id
    ) AS priceSubQ 
        ON priceSubQ.product_id = products.id
GROUP BY 
    products.id

Upvotes: 0

Related Questions