将不同的列值传递给where子句

Question

    SELECT pims.icicimedicalexaminerreport.id, 
       pims.icicimerfemaleapplicant.adversemenstrualid, 
       pims.icicimerfemaleapplicant.pregnantid, 
       pims.icicimerfemaleapplicant.miscarriageabortionid, 
       pims.icicimerfemaleapplicant.breastdiseaseid, 
       pims.pimscase.tiannumber 
FROM   pims.pimscase 
       INNER JOIN pims.digitization 
               ON pims.pimscase.digitizationid = pims.digitization.id 
       INNER JOIN pims.medicalexaminerreport 
               ON pims.digitization.medicalexaminerreportid = 
                  pims.medicalexaminerreport.id 
       INNER JOIN pims.icicimedicalexaminerreport 
               ON pims.medicalexaminerreport.id = 
                  pims.icicimedicalexaminerreport.id 
       INNER JOIN pims.icicimerfemaleapplicant 
               ON pims.icicimedicalexaminerreport.id = 
                  pims.icicimerfemaleapplicant.id 
WHERE  pims.pimscase.tiannumber = 'ICICI1234567890' 

它提供了以下输出

​

​

现在，我想使用上面的输出值从表"YesNoAnswerWithObservation“中选择行。

我想它应该看起来像这个Select * from YesNoAnswerWithObservation Where Id in (22,27,26,...23)

我只想在 in 子句中键入值，而不是在每个列中使用上述查询产生的值。

我尝试了下面的代码，但它返回表中的所有行，而不是在

SELECT pims.yesnoanswerwithobservation.observation, 
       graphitegtccore.yesnoquestion.description, 
       pims.yesnoanswerwithobservation.id ObservationId 
FROM   pims.yesnoanswerwithobservation 
       INNER JOIN graphitegtccore.yesnoquestion 
               ON pims.yesnoanswerwithobservation.yesnoanswerid = 
                  graphitegtccore.yesnoquestion.id 
WHERE  EXISTS (SELECT pims.icicimedicalexaminerreport.id, 
                      pims.icicimerfemaleapplicant.adversemenstrualid, 
                      pims.icicimerfemaleapplicant.pregnantid, 
                      pims.icicimerfemaleapplicant.pelvicorgandiseaseid, 
                      pims.icicimerfemaleapplicant.miscarriageabortionid, 
                      pims.icicimerfemaleapplicant.gynocologicalscanid, 
                      pims.icicimerfemaleapplicant.breastdiseaseid, 
                      pims.pimscase.tiannumber 
               FROM   pims.pimscase 
                      INNER JOIN pims.digitization 
                              ON pims.pimscase.digitizationid = 
                                 pims.digitization.id 
                      INNER JOIN pims.medicalexaminerreport 
                              ON pims.digitization.medicalexaminerreportid = 
                                 pims.medicalexaminerreport.id 
                      INNER JOIN pims.icicimedicalexaminerreport 
                              ON pims.medicalexaminerreport.id = 
                                 pims.icicimedicalexaminerreport.id 
                      INNER JOIN pims.icicimerfemaleapplicant 
                              ON pims.icicimedicalexaminerreport.id = 
                                 pims.icicimerfemaleapplicant.id 
               WHERE  pims.pimscase.tiannumber = 'ICICI1234567890') 

​

​

任何帮助或推动正确的方向将不胜感激。

Answer 1

您可能需要第一个查询中的_id_s：

SELECT awo.observation, ynq.description, ynq.id as ObservationId 
FROM pims.yesnoanswerwithobservation awo JOIN
     graphitegtccore.yesnoquestion ynq
      ON awo.yesnoanswerid = ynq.id 
WHERE ynq.id = (SELECT mer.id 
                FROM pims.pimscase c JOIN
                     pims.digitization d
                     ON c.digitizationid = d.id JOIN
                     pims.medicalexaminerreport mer
                     ON d.medicalexaminerreportid = mer.id JOIN
                     pims.icicimedicalexaminerreport imer
                     ON mer.id = imer.id JOIN
                     pims.icicimerfemaleapplicant ifa
                     ON imer.id = ifa.id 
                WHERE c.tiannumber = 'ICICI1234567890'
              ) ;

注意，表别名使查询更易于编写和读取。