Forum Moderators: open
The query works fine, but I need to know which of the ORs was fulfilled. Is it possible to set a temp variable to a 1 or 0 depending on which OR was fulfilled?
The query is something like
SELECT a.id, a.price, b.username, b.homepage
FROM table AS a
LEFT JOIN table2 AS b ON b.user_id = a.user_id
WHERE (created BETWEEN date_sub(now(), interval 1 hour) AND now()) OR (modified BETWEEN date_sub(now(), interval 1 hour) AND now())
AND b.enabled = 1
So if it was modified in the last hour, I'd get a "1" for the temp var, and if it was created in the last hour, I'd get a "0" (or whatever)
If there's another approach that makes more sense and I'm just missing it, please advise.
Thanks
If anyone else is searching on this, it's something like this:
SELECT a.id, a.price, b.username, b.homepage,
CASE WHEN (a.created BETWEEN date_sub(now(), interval 3 hour) AND now()) THEN 1
WHEN (a.modified BETWEEN date_sub(now(), interval 3 hour) AND now()) THEN 2
END
AS my_update
FROM table AS a
LEFT JOIN table2 AS b ON b.user_id = a.user_id
WHERE (created BETWEEN date_sub(now(), interval 3 hour) AND now()) OR (modified BETWEEN date_sub(now(), interval 3 hour) AND now())
I don't like doing the two WHERE clauses (both in the CASE and in the WHERE) but I don't know a way around that right now.
SELECT a.id, a.price, b.username, b.homepage,
CASE WHEN (a.created BETWEEN date_sub(now(), interval 3 hour) AND now()) THEN 1
WHEN (a.modified BETWEEN date_sub(now(), interval 3 hour) AND now()) THEN 2
END
AS my_update
FROM table AS a
LEFT JOIN table2 AS b ON b.user_id = a.user_id
WHERE active = 1
HAVING my_update > 0
This way you can ditch the second WHERE clause. You have to use HAVING since you can't reference an alias (my_update) from a WHERE. But this seems to work, though I'm sure it could be more efficient.
