MySQL – Complex Query Joining Multiple Tables with Aggregate Functions

aggregatejoin;MySQL

I have the following db structure: db design

Channels and packages table have many to many relations by channel_package table.

User can purchase packages or channels separately which is stored in purchases table. So when a user purchases a channel channel_id is stored as product_id and cat_id=2 and when a package is purchased, package_id is stored as product_id and cat_id=1.

What I want to get is list of channels having maximum expiry for a particular user.

If the purchases table would only store channels it would have been easy like

SELECT 
    p.id,c.name,
    max(p.expiry) 
FROM 
    purchases as p 
    LEFT JOIN channels as c 
        on c.id=p.product_id 
WHERE 
    cat_id = 2 
GROUP by c.id

So how can i get the same result considering the packages (packages contains channels, if the channel contained in the package has more expiry? The package's expiry should be displayed as max(expiry).

Best Answer

Try the following solution. Note that in your question you group by the id field in the purchases table, but that won't be possible because a single channel might be included in multiple purchases and if you group by id then you won't find the maximum across all purchases, you'll just get the maximum for each single purchase (which is really not a maximum at all).

SELECT u.user_id, u.id As channel_id, u.name As channel_name,
    max(u.expiry) As Expires
FROM (
    SELECT p.user_id, c.id, c.name, p.expiry
    FROM purchases As p
    JOIN channel_package As cp ON p.product_id = cp.package_id
    JOIN channels As c ON cp.channel_id = c.id
    WHERE p.cat = 1
    UNION ALL
    SELECT p.user_id, c.id, c.name, p.expiry
    FROM purchases As p
    JOIN channels As c On p.product_id = c.id
    WHERE p.cat = 2) As u 
GROUP by u.user_id, u.id, u.name;
Related Question