MYSQL Query using id in subquery or join

Question

I am trying to get the earliest review date for users based on a 28day, 6week and 13week cycle, when a 6week review is performed an entry is also made in the 28day table (effectively resetting it), and when a 13week review is performed an entry is made in both the 28day and 6week table.

This all works fine when I specify a specific user, but I would like to perform a select on the user table and have this calculated for each user and appended to the end of the row.

The offending line are the ones like this

WHERE `user_review_28_user_id` = '6'

that provide the list of users, but only the matching one, in this case user_id=6 is populated.

What I am trying to do is

WHERE `user_review_28_user_id` = `user_id`

but the user_id is not propagated through to the sub-query, therefore I get 'NULL' entries for the user_review_next field and user_review_next_type.

Things I have tried include JOIN and VARIABLES eg,

SELECT *, @user_id:=user_id
FROM `user`

and replacing the offending WHERE with this

WHERE `user_review_28_user_id` = @user_id

This is my query as it stands, been at it several hours and now it is holding me back :(

SELECT `user_id`, `user_first`, `user_last`, `user_review_next`, `user_review_next_type`
FROM `user`
LEFT JOIN (
SELECT *
FROM
(
    SELECT `user_review_28_user_id` as user_review_id, DATE_ADD(`user_review_28_date`, INTERVAL 28 DAY) AS 'user_review_next', '1' AS 'user_review_next_type'
    FROM `user_review_28`
    WHERE `user_review_28_user_id` = '6'
    UNION
    SELECT `user_id` as user_review_id, DATE_ADD(`user_start_date`, INTERVAL 28 DAY) AS 'user_review_next', '1' AS 'user_review_next_type'
    FROM `user`
    WHERE `user_id` = '6'
    ORDER BY `user_review_next` DESC
    LIMIT 1
) AS tmp_28d
UNION
SELECT * 
FROM
(
    SELECT `user_review_6_user_id` as user_review_id, DATE_ADD(`user_review_6_date`, INTERVAL 6 WEEK) AS 'user_review_next', '2' AS 'user_review_next_type'
    FROM `user_review_6`
    WHERE `user_review_6_user_id` = '6'
    UNION
    SELECT `user_id` as user_review_id, DATE_ADD(`user_start_date`, INTERVAL 6 WEEK) AS 'user_review_next', '2' AS 'user_review_next_type'
    FROM `user`
    WHERE `user_id` = '6'
    ORDER BY `user_review_next` DESC
    LIMIT 1
) AS tmp_6w
UNION
SELECT * 
FROM
(
    SELECT `user_review_13_user_id` as user_review_id, DATE_ADD(`user_review_13_date`, INTERVAL 13 WEEK) AS 'user_review_next', '3' AS 'user_review_next_type'
    FROM `user_review_13`
    WHERE `user_review_13_user_id` = '6'
    UNION
    SELECT `user_id` as user_review_id, DATE_ADD(`user_start_date`, INTERVAL 13 WEEK) AS 'user_review_next', '3' AS 'user_review_next_type'
    FROM `user`
    WHERE `user_id` = '6'
    ORDER BY `user_review_next` DESC
    LIMIT 1
) AS tmp_13w
ORDER BY user_review_next ASC, user_review_next_type DESC
LIMIT 1
) AS tmp_user_review
ON user.user_id = tmp_user_review.user_review_id

This is an example output from the query as shown above.

1   David   Berry       NULL        NULL
2   Joseph  Armstrong   NULL        NULL
3   Thomas  Brown       NULL        NULL
4   Paul    Armstrong   NULL        NULL
5   Calum   Blair       NULL        NULL
6   Craig   Bridges     2015-05-27  1
7   Donald  Branscombe  NULL        NULL
8   Kenneth Bacon       NULL        NULL
9   Jason   Bambrick    NULL        NULL

SQLFiddle

Graham · Accepted Answer

This is what I have come up with, but now I get an error

#1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

Unless I include the line SET SQL_BIG_SELECTS=1; before the query, so I am hoping someone will still jump in and help my sort my SQL woes

SELECT `user_id`,
`user_first`,
    `user_last`,
    LEAST
    (
        DATE_ADD(user_review_28_date, INTERVAL 28 DAY),
            DATE_ADD(user_review_6_date, INTERVAL 6 WEEK),
            DATE_ADD(user_review_13_date, INTERVAL 13 WEEK)
    ) AS user_review_next_date,
    CASE
       WHEN LEAST
        (
                DATE_ADD(user_review_28_date, INTERVAL 28 DAY),
                DATE_ADD(user_review_6_date, INTERVAL 6 WEEK),
                DATE_ADD(user_review_13_date, INTERVAL 13 WEEK)
        ) = DATE_ADD(user_review_28_date, INTERVAL 28 DAY) THEN '1'
        WHEN LEAST
        (
                DATE_ADD(user_review_28_date, INTERVAL 28 DAY),
                DATE_ADD(user_review_6_date, INTERVAL 6 WEEK),
                DATE_ADD(user_review_13_date, INTERVAL 13 WEEK)
        ) = DATE_ADD(user_review_6_date, INTERVAL 6 WEEK) THEN '2'
        WHEN LEAST
        (
                DATE_ADD(user_review_28_date, INTERVAL 28 DAY),
                DATE_ADD(user_review_6_date, INTERVAL 6 WEEK),
                DATE_ADD(user_review_13_date, INTERVAL 13 WEEK)
        ) = DATE_ADD(user_review_13_date, INTERVAL 13 WEEK) THEN '3'
    END AS user_review_next_type
FROM `user` AS a
LEFT JOIN
(
SELECT user_review_28_user_id, user_review_28_date
    FROM `user_review_28`) AS b_28
ON a.user_id = b_28.user_review_28_user_id
        AND b_28.user_review_28_date=(SELECT MAX(user_review_28_date)
    FROM `user_review_28` AS c_28
    WHERE a.user_id = c_28.user_review_28_user_id
)
LEFT JOIN
(
SELECT user_review_6_user_id, user_review_6_date
    FROM `user_review_6`) AS b_6
ON a.user_id = b_6.user_review_6_user_id
        AND b_6.user_review_6_date=(SELECT MAX(user_review_6_date)
    FROM `user_review_6` AS c_6
    WHERE a.user_id = c_6.user_review_6_user_id
)
LEFT JOIN
(
SELECT user_review_13_user_id, user_review_13_date
    FROM `user_review_13`) AS b_13
ON a.user_id = b_13.user_review_13_user_id
        AND b_13.user_review_13_date=(SELECT MAX(user_review_13_date)
    FROM `user_review_13` AS c_13
    WHERE a.user_id = c_13.user_review_13_user_id
)

WHERE a.user_assessor_id ='5'
ORDER BY user_review_next_date ASC, user_review_next_type DESC

MYSQL Query using id in subquery or join

Answers (2)

Related Questions