mysql - chaining joins back to target table -

mysql - chaining joins back to target table -

i have 4 tables

survey question responses participants ============= ============== ============== ============= id_s id_q id_r id_p id_s id_q id_s id_p id_s reply

i'm trying figure out how retrieve responses participants gave "d" reply question #3.

this first wack @ unsurprisingly doesn't work.

select * responses r left bring together participants p on r.id_p = p.id_p left bring together responses r2 on p.id_r = r.id_p r.id_s = 1 , r2.answer = "d" , r2.id_q = 1

by doesn't work returns many records. sql select * responses id_s =1 homecoming 1,891 records, above hot mess returns 15,128 records.

clearly don't have right concept of how work, , it's not simple syntax tweak that's needed.

select ra.* responses rd bring together responses ra on (ra.id_s, ra.id_p) = (rd.id_s, rd.id_p) (rd.id_q, rd.answer) = (3, 'd')

mysql sql