[SOLVED] Modifying SQL to use a different table in a select statement

Modifying SQL to use a different table in a select statement

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).

Solution

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
  --