链接连接回目标表 [英] chaining joins back to target table

问题描述

我有四张桌子

Survey          Question        Responses       Participants
=============   ==============  ==============  =============
id_S            id_Q            id_R            id_P
                id_S            id_Q            id_S
                                id_P
                                id_S
                                Answer

我想弄清楚如何为问题 3 的答案为D"的参与者检索所有答案.

I'm trying to figure out how to retrieve all the Responses for the Participants who gave "D" as the answer for Question #3.

这是我第一次尝试它，但不出所料，它不起作用.

This is my first wack at it but unsurprisingly it doesn't work.

SELECT * 
FROM responses r
LEFT JOIN participants p
ON r.id_P = p.id_P
LEFT JOIN responses r2
ON p.id_R = r.id_P
WHERE r.id_S = 1 AND r2.Answer = "D" AND r2.id_Q = 1

By 不起作用，它返回太多记录.SQL Select * FROM response WHERE id_S =1 将返回 1,891 条记录，但上面的热乱码返回 15,128 条记录.

By doesn't work it returns too many records. The SQL Select * FROM responses WHERE id_S =1 would return 1,891 records, but the above hot mess returns 15,128 records.

很明显，我什至没有正确的概念来说明如何让它工作，而且这不是一个简单的语法调整所需要的.

Clearly I don't even have the right concept of how to get this to work, and it's not a simple syntax tweak that's needed.

推荐答案

SELECT  ra.*
FROM    responses rd
JOIN    responses ra
ON      (ra.id_s, ra.id_p) = (rd.id_s, rd.id_p)
WHERE   (rd.id_q, rd.answer) = (3, 'D')

这篇关于链接连接回目标表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！