Subquery returned more than 1 value in sql server

Question

    SELECT * FROM TableC
     INNER JOIN TableB ON TableB.mid=TableC.mid
     INNER JOIN TableA ON TableA.userid=(
                                         SELECT distinct userid 
                                           FROM TableB)

Subquery returned more than 1 value.

Medical_Master

       MedicalID MedicalName
       1(pk)     abc
       2         xyx
       3         pqr

Child_Medical_Master

       ChildMID MedicalID Station Name
       1(pk)    1(fk)     bnb     mfk
       2        1         def     rwr
       3        2         re      wrw      

Medical_Visit

       VTID  PMID  RFMID age
       1(pk) 2(fk) 1      34
       2     2     3      45
       3     3     1      45
       4     1     2      44
       5     2     2      76

Medical_Study

       UID   VTID  ChildMID SMID   Date  time 
       1(pk) 1(fk) 1        1      kk    jdj
       2     2     3        2      kdf   lfl
       6     3     2        3      rgr   rtr

Doctor_Master

       RFMID  Doctorname
       1(pk)  mr.john
       2      mr.jack
       3      mr.jim

PAtient_Master

       PMID   Firstname LastNAme
       1(pk)  df         ere
       2      rwe        rwer
       3      rwr        fwr

Study_Master

       SMID   MedicalID Description Duration
       1(pk)  1(fk)     fdf         efe
       2      1         ddf         dfdf
       3      2         df          ef

I want these columns from tables how should be my correct query?

UID,PMID,FIRSTNAME,LASTNAME,AGE,MEDICALNAME,DESCRIPTION,STATION,DATE,DoctorName

Answer 1

Assuming you don't want to do a normal join and there is a purpose to the subquery over a normal join:

You either need to limit what is coming into the subquery like this:

select * from TableC
inner join TableB on TableB.mid=TableC.mid
inner join TableA on TableA.userid=(select distinct userid from TableB where userid=3)

or change your main query like this:

select * from TableC
inner join TableB on TableB.mid=TableC.mid
inner join TableA on TableA.userid in (select distinct userid from TableB)

Okay, got the code and made a sqlfiddle for you to see it working.

select
    medical_study.uid,
    patient_master.PMID,
    patient_master.firstname,
    patient_master.surname,
    medical_visit.age,
    medical_master.medicalName,
    study_master.descripto,
    child_medical_master.station,
    medical_study.dater,
    doctor_master.doctorname
from
    medical_master
    join child_medical_master
        on medical_master.medicalID=child_medical_master.medicalID
    join medical_study
        on child_medical_master.childMID=medical_study.childMID
    join medical_visit
        on medical_study.VTID=medical_visit.VTID
    join doctor_master
        on medical_visit.RFMID=doctor_master.RFMID
    join patient_master
        on medical_visit.PMID=patient_master.PMID
    join study_master
        on medical_master.medicalID=study_master.medicalID

Answer 2

try this:

 select * from TableC
    inner join TableB on TableB.mid=TableC.mid
    inner join TableA on TableA.userid=TableB.userid