Modifying SQL to use a different table in a select statement

Question

I'm trying to change the date search behavior in my roundup install, but SQL is not my strong suit. Here is the existing query:

select _issue.id,_issue._activity,(_issue._activity is not NULL),
    _priority3._order,(_priority3._order is not NULL) from _issue
LEFT OUTER JOIN _priority as _priority3
    on _issue._priority=_priority3.id
where _issue._activity >= '2015-01-16 08:00:00.000' and
    _issue._activity <= '2015-01-17 07:59:59.000' and
    _issue.__retired__=0
order by (_priority3._order is not NULL),_priority3._order,
    (_issue._activity is not NULL),_issue._activity,_issue.id

Here are the tables with the interesting fields:

table:  _issue
fields: id         (integer)
        _activity  (timestamp)
        _priority  (integer)

table:  _priority  (alias priority3)
fields: id         (integer)
        order      (real)

The tables I need to use:

table:  issue_messages
fields: linkid     (integer, id of message)
        nodeid     (integer, id of issue)

table:  _msg
fields: id         (integer)
        _activity  (timestamp)

What I want is a list of issues that had any activity, which means checking the message activity date, not the issue activity date (which only reflects the last activity).

Answer 1

It sounds like this is approximately what you are after:

SELECT
  --
  -- The fields you want to return
  --
FROM _issue, issue_messages, _msg, _priority AS _priority3 -- I emulated your alias
WHERE
  _issue.id = issue_messages.nodeid AND
  issue_messages.linkid = _msg.id AND
  _issue._priority = _priority3.id AND
  _msg._activity BETWEEN '2015-01-16 08:00:00.000' AND '2015-01-17 07:59:59.000'
  --
  -- And maybe other filtering conditions you need
  --
ORDER BY
  --
  -- The fields by which you want to order
  --