CODEWITHSUNDEEP
mysqljoinsubquery
Herr
Herr

Reputation: 2735

Sub query or Join which is the optimal solution?

i've 2 tables, 1 user table and a table where the
outgoing emails are queued. I want to select the users
that are not online for a certain amount of time
and send them an email. I also want that, if they
already received such an email in the last 7 days
or have an scheduled email for the next 7 days, that
they are not selected.

I have 2 queries, which i think would be great if
they are working with subqueries.

As an area of which i'm not an expert in, i would
like to kindly invite you to either,

  1. Build a subquery of the second query
  2. Make a JOIN and exclude the second query results.

I would be far more then happy :)

Thank you for reading

SELECT
    `user_id`
FROM
    `user`
WHERE
    DATEDIFF( CURRENT_DATE(), date_seen ) >= 7

The results of the second query should be excluded
from the query above.

SELECT 
    `mail_queue_id`,
    `mail_id`,
    `user_id`,
    `status`,
    `date_scheduled`,
    `date_processed`
FROM
    `mail_queue`
WHERE
    ( 
        DATEDIFF( CURRENT_DATE(), date_scheduled ) >= 7 
            OR
        DATEDIFF( date_scheduled, CURRENT_DATE() ) <= 7
    )
    AND
    (

        `mail_id` = 'inactive_week'

        AND
        (
            `status` = 'AWAITING'
            OR
            `status` = 'DELIVERED'
        )

    )

SOLUTION

SELECT
    `user_id`
FROM
    `user` as T1
WHERE
    DATEDIFF( CURRENT_DATE(), date_seen ) >= 7 
    AND NOT EXISTS
    (

        SELECT 
            `user_id`    
        FROM
            `mail_queue` as T2
        WHERE
            T2.`user_id` = T1.`user_id`
            AND
            (

                DATEDIFF( CURRENT_DATE(), date_scheduled ) >= 7 
                    OR
                DATEDIFF( date_scheduled, CURRENT_DATE() ) <= 7

                AND
                (

                    `mail_id` = 'inactive_week'

                    AND
                    (
                        `status` = 'AWAITING'
                        OR
                        `status` = 'DELIVERED'
                    )


                )



            )


    )

Upvotes: 0

Views: 169

Answers (1)

Tim
Tim

Reputation: 5421

YOu can select the users who match the first criterion (not having logged on in the past seven days) and then "AND" that criterion to another clause using "NOT EXISTS", aliasing the same table:

        select * from T where {first criterion}
        and not exists
        (
          select  * from T as T2 where T2.userid = T.userid
          and ABS( DATEDIFF(datescheduled, CURRENT_DATE()) ) <=7

       )

I'm not familiar with the nuances of the mysql DATEDIFF, i.e. whether it matters which date value appears in which position, but the absolute value would make it so that if the user had been sent a notice in the past 7 days or is scheduled to receive a notice in the next seven days, they would satisfy the condition, and thereby fail the NOT EXISTS condition, excluding that user from your final set.

Upvotes: 1

Related Questions