complex query joining multiple tables with aggregate functions I have the following db structure: 

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).
Check the result here: http://rextester.com/RQKTP46266

I made it in postgresql, but I think its the same for mysql.

This is the structure used:

create temp table ch (cid int, name text);
create temp table pk (pid int, name text);
create temp table pkch (id int, pid int, cid int);
create temp table pu (id int, userid int, prid int, cat int, expiry date);

insert into ch values (1, 'channel 1');
insert into ch values (2, 'channel 2');
insert into ch values (3, 'channel 3');
insert into ch values (4, 'channel 4');
insert into ch values (5, 'channel 5');

insert into pk values (1, 'pack 1');

insert into pkch values (1, 1, 1);
insert into pkch values (2, 1, 2);
insert into pkch values (3, 1, 3);

insert into pu values (1, 10, 1, 1, '2016-05-01');
insert into pu values (2, 10, 2, 2, '2016-04-01');
insert into pu values (3, 10, 3, 2, '2016-04-01');


First you should expand channels included in purchased packages, and UNION with the single channels.

The next step is easy, select max(expiry) and goup it by channel.

select userid, channel_id, max(expiry)
from
(
    select pu.id, pu.userid, pkch.cid as channel_id, pu.expiry
    from pu
        join pkch on pu.prid = pkch.pid and pu.cat = 1
    where pu.userid = 10
    UNION
    select pu.id, pu.userid, pu.prid as channel_id, expiry
    from pu
    where pu.userid = 10 and pu.cat = 2
) a
group by userid, channel_id