sql - 可能的嵌套查询

Question

我有以下功能，非常有用。

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

字段parent_session_id(整数)可以存储之前记录的主ID，否则默认为0。如果可能的话，我想做的是使用嵌套或子查询以下:

1. 检索上述所有内容，但删除任何记录，如果它的主 ID 正被 parent_session_id 字段中的另一个人使用。

2. 如果多条记录引用了parent_session_id字段中的同一条记录(> 0)，只根据时间戳获取最新的一条(s.start_time DESC LIMIT 1 )

我觉得如果不使查询变得异常复杂，这是不可能的，但我的查询技能并没有比我上面的更深入。

Answer 1

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.

假设您的主 ID 是 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
<b>AND    NOT EXISTS (
    SELECT 1
    FROM   tblSessions s1
    WHERE  s1.parent_session_id = s.session_id
    )</b>
ORDER  BY s.start_time, s.last_name, s.first_name;

你的第二个问题与第一个问题矛盾。所以，我就此打住:

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)