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).