1

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

1

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;
  • i have group by channel id, is it necessary to group by 3 cols, i think grouping by u.channel_id works for a particular user – avash Dec 4 '16 at 10:34
  • Your question said that you want the results by user so I included the user_id. In this case, since channels.name is functionally dependent on channels.id it would be permissible to omit it from the GROUP BY but some consider that non-best practice. – mendosi Dec 4 '16 at 10:48
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.