CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008t-sql
MarceloMadnezz
MarceloMadnezz

Reputation: 307

SQL - Select Boolean Results from Table

Well ,I didn't find a correct title for this question, sorry about that.

I Have one table where I store some emails sent to users.

In this table I can know if the user read or not the email.

Table structure:

[MAILSEND_ID] (INT),
[ID_USER] (INT), 
[MAIL_ID] (INT), 
[READ] (BIT)

Data:

;WITH cte AS (
SELECT * FROM (VALUES
(1, 10256, 10, 0),
(1, 10257, 10, 1),
(1, 10258, 10, 1),
(1, 10259, 10, 0),
(2, 10256, 10, 0),
(2, 10257, 10, 0),
(2, 10258, 10, 1),
(2, 10259, 10, 0),
(3, 10256, 10, 1),
(3, 10257, 10, 0),
(3, 10258, 10, 0),
(3, 10259, 10, 0)
) as t(MAILSEND_ID, ID_USER, MAIL_ID, READ)

In this example, you can see, i have 4 Users and 3 Emails Sent. 

User 10256
1st Email - Don't Read
2nd Email - Don't Read
3rd Email - Read

I need make a select on this table, that I give the [MAIL_ID] and a [NUMBER], this number represent the sequential e-mails that is not read by the user.

Using the last example:

Give the [NUMBER] = 3, [MAIL_ID] = 10

Return the USER_ID 10259 only.

Give the [NUMBER] = 2, [MAIL_ID] = 10

Return the USER_ID 10257, 20259.

Give the [NUMBER] = 1, [MAIL_ID] = 10

Return the USER_ID 10257, 10258, 20259.

In another words, the USER_ID can have one accumulated number of e-mails not read, but if this user read the last e-mail, he cant be returned in the query.

This is my query today, but only returns the total of emails not read:

select * from (
select 
    a.[USER_ID], 
    COUNT(a.[USER_ID]) as tt 
from 
    emailmkt.mailing_history a 
where 
    a.[MAIL_ID] = 58 and 
    a.[READ]=0 
group by 
    [USER_ID]
) aa where tt > [NUMBER]

So the logic is not right. I Want to transfer this logic to SQL and not do this on Code, if is possible.

Sorry if have any english errors as well.

Thanks in advance.

Upvotes: 1

Views: 94

Answers (1)

Serpiton
Serpiton

Reputation: 3684

With the following query you can get the rolling count of the mail to read by user, based of the hypothesis that mailsend_id is time related (I changed READ to IsRead 'cause I don't have the char ` on my keyboard)

SELECT ID_USER, Mail_ID
     , groupid CURRENT
     , @roll := CASE WHEN coalesce(@groupid, '') = groupid 
                     THEN @roll + 1 
                     ELSE 1 
                END AS roll
     , @groupid := groupid OLD
FROM   (SELECT mh.ID_USER, mh.Mail_ID
             , concat(mh.id_user, mh.mail_id) groupid
        FROM mailing_history mh
             INNER JOIN (SELECT   id_user
                                , max(CASE isread 
                                           WHEN 1 THEN MAILSEND_ID 
                                           ELSE 0 
                                      END) lastRead
                         FROM     mailing_history
                         GROUP BY id_user) lr
     ON mh.id_user = lr.id_user AND mh.MAILSEND_ID > lr.lastread
ORDER BY id_user, MAILSEND_ID) a

Demo: SQLFiddle

The column Roll has the rolling count of the mail to read for the user.
Adding a level you can check the value of Roll against NUMBER in a WHERE condition and group_concat the user_id

Upvotes: 2

Related Questions