zode64 zode64 - 7 months ago 8
SQL Question

How can adding an OR conditional to a inner select reduce the number of results using a mongo jdbc driver

If I run the following query through a mongo jdbc driver I get 3 results with my existing data set

SELECT a1.ACTIVITY,
a1.START_TIME,
a1.END_TIME,
FROM ACTIVITIES a1
INNER JOIN (SELECT MAX(ai._ID)
AS maxid
FROM ACTIVITIES ai
WHERE ai.activity = 'writing'
GROUP BY ai.ACTIVITY_CONTAINER_ID) a2 ON (a2.maxid = a1._ID)
LEFT JOIN PROJECT_TASKS pt ON a1.ACTIVITY_CONTAINER_ID = pt._ID
LEFT JOIN USERS u ON a1.ACTOR_ID = u._ID
LEFT JOIN PROJECTS p ON pt.PROJECT_ID = p._ID


If I then add
OR ai.activity = 'editing'
to the inner select I only get 1 result. How can adding this OR conditional to the inner select reduce the number of results

SELECT a1.ACTIVITY,
a1.START_TIME,
a1.END_TIME,
FROM ACTIVITIES a1
INNER JOIN (SELECT MAX(ai._ID)
AS maxid
FROM ACTIVITIES ai
WHERE ai.activity = 'writing'
OR ai.activity = 'editing'
GROUP BY ai.ACTIVITY_CONTAINER_ID) a2 ON (a2.maxid = a1._ID)
LEFT JOIN PROJECT_TASKS pt ON a1.ACTIVITY_CONTAINER_ID = pt._ID
LEFT JOIN USERS u ON a1.ACTOR_ID = u._ID
LEFT JOIN PROJECTS p ON pt.PROJECT_ID = p._ID

Answer

I used something like the query below in the end which from what I gather does the same thing but slower. I suspect the error I was seeing is due to strange behavior of a mongo JDBC driver I was passing the queries to which converted them to mongo query language. I updated the question to make it more useful.

SELECT
  a1.ACTIVITY,
  a1.START_TIME,
  a1.END_TIME,
FROM ACTIVITIES a1
INNER JOIN (
SELECT MAX(ai._ID) as maxid
                  FROM ACTIVITIES ai
                  WHERE ai.ACTIVITY = 'writing'
                  GROUP BY ai.ACTIVITY_CONTAINER_ID
UNION ALL
SELECT MAX(ai._ID) as maxid
                  FROM ACTIVITIES ai
                  WHERE ai.ACTIVITY = 'editing'
                  GROUP BY ai.ACTIVITY_CONTAINER_ID) as a2
ON (a2.maxid = a1._ID)
LEFT JOIN PROJECT_TASKS pt
  ON a1.ACTIVITY_CONTAINER_ID = pt._ID
LEFT JOIN USERS u
  ON a1.ACTOR_ID = u._ID
LEFT JOIN PROJECTS p
  ON pt.PROJECT_ID = p._ID 
Comments