Nested Query Possible

Question

I have the following that works like a charm.

SELECT d.decisionName, c.firstname, c.lastname, o.name AS organization_name, s.parent_session_id
    FROM tblDecisions d
        INNER JOIN tblSessions s ON s.decision_id = d.decisionid
        INNER JOIN tblCounselors c ON s.counselor_ck = c.campusid
        INNER JOIN tblCounselor_to_organization co ON co.counselor_id = c.counselorid
        INNER JOIN tblOrganizations o ON o.organizationid = co.organization_id
    AND s.start_time >= '2011-01-01 00:00:00'
    AND s.is_complete = TRUE
    ORDER BY s.start_time, s.last_name, s.first_name

The field parent_session_id (integer) can store the primary ID of a previous record, otherwise it defaults to 0. What I would like to do, if possible, is to use nested or subqueries for the following:

1. Retrieve all the above, but remove any record if it's primary ID is being used by another in the parent_session_id field.

2. If multiple records reference the same record in parent_session_id field (> 0), only get the latest one by time stamp (s.start_time DESC LIMIT 1)

I have a feeling this won't be possible without making the query ridiculously complex, but my query skills don't get much deeper than what I have above.

Answer 1

I don't think the second question contradicts the first. The first is saying, in essence, if this session is parent to any others, don't include it in the results. The second question is saying, if more than one session has the same parent, only include the latest child.

Here is my solution incorporating both subquestions:

SELECT d.decisionName, c.firstname, c.lastname, o.name AS organization_name,  s.parent_session_id
, s.start_time, (SELECT max(start_time)
                      FROM tblSessions s2
                      WHERE s2.parent_session_id = s.parent_session_id)
FROM tblDecisions d
    INNER JOIN tblSessions s ON s.decision_id = d.decisionid
    INNER JOIN tblCounselors c ON s.counselor_ck = c.campusid
    INNER JOIN tblCounselor_to_organization co ON co.counselor_id = c.counselorid
    INNER JOIN tblOrganizations o ON o.organizationid = co.organization_id
AND s.start_time >= '2011-01-01 00:00:00'
AND s.is_complete = 1
AND NOT EXISTS (
  SELECT 1
  FROM   tblSessions s1
  WHERE  s1.parent_session_id = s.sessionid
  )
AND (
     (s.parent_session_id IS NULL)
  OR (s.start_time = (SELECT max(start_time)
                      FROM tblSessions s2
                      WHERE s2.parent_session_id = s.parent_session_id))) 

ORDER BY s.start_time, s.last_name, s.first_name

Here is an SQLFiddle demonstrating it.

Note that I used SQL Server, not MySQL. But I believe the solution to be easily convertible; I think only the is_complete = 1 needs to change to is_complete = true.

Answer 2

1.) Retrieve all the above, but remove any record if it's primary ID is being used by another in the parent_session_id field.

Assuming that your primary ID is tblSessions.session_id:

SELECT d.decisionName, c.firstname, c.lastname, o.name AS organization_name
                                              , s.parent_session_id
FROM   tblDecisions                 d
JOIN   tblSessions                  s ON s.decision_id = d.decisionid
JOIN   tblCounselors                c ON c.campusid = s.counselor_ck
JOIN   tblCounselor_to_organization co ON co.counselor_id = c.counselorid
JOIN   tblOrganizations             o ON o.organizationid = co.organization_id
AND    s.start_time >= '2011-01-01 00:00:00'
AND    s.is_complete
AND    NOT EXISTS (
    SELECT 1
    FROM   tblSessions s1
    WHERE  s1.parent_session_id = s.session_id
    )
ORDER  BY s.start_time, s.last_name, s.first_name;

Your second question contradicts the first. So, I'll leave it at that:

2.) If multiple records reference the same record in parent_session_id field (> 0), only get the latest one by time stamp (s.start_time DESC > LIMIT 1)