Whats the best way to do this?

[hexOffender]

Here is the query I am working with:

Select distinct ADV.VisitID,ADV.AccountNumber,(select distinct InsuranceName from AdmInsuranceOrder where InsuranceOrderID='1'and VisitID=ADV.VisitID) as PrimaryInsurance,
(select InsuranceName as SecondaryInsurance from AdmInsuranceOrder where InsuranceOrderID='2'and VisitID=ADV.VisitID ) as SecondaryInsurance
from AdmInsuranceOrder
Inner Join AdmVisits as ADV on ADV.VisitID=AdmInsuranceOrder.VisitID

I need to pull the ADV.VisitID into the subquery for VisitID. Do I have to use a cursor to do this? I really dont want to because the output looks ugly.

--

 

[bborissov]

Select ADV.VisitID,
       ADV.AccountNumber,
       MAX(CASE WHEN InsuranceOrderID='1'
                THEN  InsuranceName
           ELSE '' END) AS PrimaryInsurance,

       MAX(CASE WHEN InsuranceOrderID='2'
                THEN  InsuranceName
           ELSE '' END) AS SecondaryInsurance
from AdmInsuranceOrder
Inner Join AdmVisits as ADV on ADV.VisitID=AdmInsuranceOrder.VisitID
GROUP BY ADV.VisitID,
         ADV.AccountNumber

NOT TESTED!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[hexOffender]

wow that works great thank you.

 

[SQLSister]

hex Offender, anytime you have a correlated subquery you can pretty much be sure there is a better way to the same job using joins. Correlated subqueries are performance killers.

"NOTHING is more important in a database than integrity." ESquared

 

[hexOffender]

SQLsister -Yeah I've noticed, so is Boris's solution the best way?

 

[jshurst]

That way looks good to me. Someone told me not to use cursors when I first began programming and I have been able to get around them 99.9% of the time.

On another note... group by's (and order bys) can be really slow on really large datasets. They are often neccessary though. Many times if a query is running slow if I rewrite it to use table variables with smaller datasets, then join and group, then finally return the results it can drastically improve performance (if a dataset is still really large I might use a temp table rather than a table variable as well).

Not saying it will help here (especially since you just have one join), but just wanted to mention it.

Regards,

J