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

See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 You need to use one of these techniques to get the latest row for each user ID in the subquery, and then join with that. — Barmar, May 20 '15 at 19:50
Barmar, I have read the page that you have referred to and I cannot quite get my head around how to apply it, could you possibly provide a snippet of psudocode that demonstrates it in this situation and I can build the query up from that? Thanks — Graham, May 20 '15 at 20:10
Doing it with a query like this will be complicated, because you need to join the query with itself, which means repeating that giant `UNION`. Try creating a view that performs the `UNION`. Then you should be able to use the view in place of the table name in the answers to that question. — Barmar, May 20 '15 at 20:13
I can try, not something I have done before, but give me a few minutes. — Graham, May 20 '15 at 20:23
Added a SQLFiddle to the end of the question - just above the TAGS. Thanks — Graham, May 20 '15 at 20:44

WilliamSF · Answer 1 · 2015-05-20T19:31:27.017

Have you tried this?

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`
    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`
    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`
    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
WHERE tmp_user_review= '6'

Just tried this and the response was, #1054 - Unknown column 'myuser' in 'where clause'. This is what I have been getting with most of my attempts. — Graham, May 20 '15 at 19:24
Hi Thanks for the suggestions, but this one has not worked either. I will amend my question to show a snippet from the results. — Graham, May 20 '15 at 19:48

score 0 · Accepted Answer · answered May 21 '15 at 01:24

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

By changing the joins to inner joins and ensuring every review has a row in the tables when a new user is created this seems to be working as intended. — Graham, May 21 '15 at 17:10

MYSQL Query using id in subquery or join

2 Answers2