How do I get data from the table using the subquery in sql server 2008?

advertisements

I am trying to get data from tables using sub query, but i got this error :

Base table : [tblPriscription]

[Priscriptionid] [bigint] IDENTITY(1,1) NOT NULL,
[patientId] [bigint] NULL,
[doctorId] [bigint] NULL,
[BillNo]  AS ([Priscriptionid]+(100)),
[BillDate] [datetime] NULL,
[BillType] [nvarchar](50) NULL,
[PaymentBy] [nvarchar](50) NULL,
[DocumentType] [nvarchar](50) NULL,
[DocumentName] [nvarchar](50) NULL,
[bitIsActive] [bit] NULL,
[dateCreated] [date] NULL,
[bitIsDelete] [bit] NULL,
[bitisSave] [bit] NULL,
[TotalAmount] [decimal](18, 2) NULL,

A table from which i want to fetch data:

dbo.tblPriscriptionDetail

[PriscriptionDtlid] [bigint] IDENTITY(1,1) NOT NULL,
[Priscriptionid] [bigint] NULL,
[drugId] [bigint] NULL,
[Rxno] [bigint] NULL,
[sigId] [bigint] NULL,
[Selling] [decimal](18, 2) NULL,
[Qty] [int] NULL,
[RefillQty] [int] NULL,
[RefillNo] [int] NULL,
[Days] [int] NULL,
[Amount] [decimal](18, 2) NULL,
[bitIsActive] [bit] NULL,
[dateCreated] [datetime] NULL,
[bitIsDelete] [bit] NULL,
[PurchaseDtlid] [bigint] NULL,
[bitIsSave] [bit] NULL,
[BillType] [nvarchar](50) NULL,
[PaymentBy] [nvarchar](50) NULL,
[patientId] [bigint] NULL,
[id] [bigint] NULL,

Qyery:

SELECT *,
       (SELECT insuranceName
        FROM   tblinsurance
        WHERE  insuranceid = (SELECT insuranceid
                              FROM   tblpatient
                              WHERE  PatientId = tblPriscription.PatientId))            AS insuranceName,
       (SELECT drfirstname + ' ' + drlastname
        FROM   tbldoctor
        WHERE  doctorid = tblPriscription.doctorid)                                     AS doctorname,
       (SELECT patFirstName + ' ' + patLastName
        FROM   tblPatient
        WHERE  patientid = tblPriscription.patientid)                                   AS patname,
       (SELECT policyno
        FROM   tblPatient
        WHERE  patientid = tblPriscription.patientid)                                   AS policyno,
       (SELECT insuranceplanname
        FROM   tblInsurancePlan
        WHERE  insuranceplanid = (SELECT insuranceplanid
                                  FROM   tblpatient
                                  WHERE  PatientId = tblPriscription.PatientId))        AS insurancePlanName,
       (SELECT drugname
        FROM   tblDrugMaster
        WHERE  drugid = (SELECT drugid
                         FROM   tblPriscriptionDetail
                         WHERE  priscriptionid = tblPriscriptionDetail.priscriptionid)) AS drugname,
       (SELECT qty
        FROM   tblPriscriptionDetail
        WHERE  priscriptionid = tblPriscriptionDetail.priscriptionid)                   AS qty,
       (SELECT Selling
        FROM   tblPriscriptionDetail
        WHERE  priscriptionid = tblPriscriptionDetail.priscriptionid)                   AS selling
FROM   tblPriscription

i am trying to fetch data from tblPriscriptionDetail drugname,qty and Selling suggest me, how to do that


You simply need join not messy sub query.

  SELECT tp.*, tpd.qty, tpd.Selling, ti.insuranceName
    FROM tblPriscription tp inner join tblPriscriptionDetail tpd ON tpd.Priscriptionid = tp.Priscriptionid
inner join tblinsurance ti ON ti.insuranceid  = tp.PatientId
inner join your other tables