Subselect top1 - need two columns.

[CodeButcher]

I have a subselect in my select statement:
select t1.a, t1.b, (select top 1 t2.a from where)

what I need is:
select t1.a, t1.b, (select top 1 t2.a, t2.b from where)

However, I get the error that I can only have one field.

Here is the sql. I'm stumped. Thanks for your help...

Select
cm.ChildStateID ,
pc.DateOfServic e,

(SELECT
TOP 1 pcr.specialtyid , sp.Description
FROM
ProviderAccount pa,
ProviderPayee pp,
Provider pr,
ProviderCredent ial pcr,
Specialty sp
WHERE
pca.ProviderID = PP.TaxID
and right(pca.Provi derSuffix,4) = substring(pa.Pa yeeSeq,1,4)
and PP.ID = PA.PayeeID
and PA.ProviderID = PR.ID
and pcr.ProviderID = PR.id
and pcr.SpecialtyID = sp.id)

from
Childmaster cm,
pclaim pc,
pclaimaux pca

where
pca.childid = cm.childstateid
and pc.id = pca.pclaimid

order by
cm.ChildStateID ,
pc.DateOfServic e

[ck9663]

What version of SQL Server are you using?

As you can see in this samples, TOP keyword can work in multiple columns.

-- CK

[CodeButcher]

Yes, I see it works for multiple columns but not in a sub-select.

SQL Server 2000.

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

[ck9663]

I have a subselect in my select statement:
select t1.a, t1.b, (select top 1 t2.a from where)

what I need is:
select t1.a, t1.b, (select top 1 t2.a, t2.b from where)

However, I get the error that I can only have one field.

Here is the sql. I'm stumped. Thanks for your help...

Select
cm.ChildStateID ,
pc.DateOfServic e,

(SELECT
TOP 1 pcr.specialtyid , sp.Description
FROM
ProviderAccount pa,
ProviderPayee pp,
Provider pr,
ProviderCredent ial pcr,
Specialty sp
WHERE
pca.ProviderID = PP.TaxID
and right(pca.Provi derSuffix,4) = substring(pa.Pa yeeSeq,1,4)
and PP.ID = PA.PayeeID
and PA.ProviderID = PR.ID
and pcr.ProviderID = PR.id
and pcr.SpecialtyID = sp.id)

from
Childmaster cm,
pclaim pc,
pclaimaux pca

where
pca.childid = cm.childstateid
and pc.id = pca.pclaimid

order by
cm.ChildStateID ,
pc.DateOfServic e

Sorry I missed that part.It looks to me you're trying to include the result of your subquery to every record on your main query. Can you see if this will work?

Select 
   cm.ChildStateID, 
	pc.DateOfService,
from
(Select  
	'1' as mainlink, cm.ChildStateID, 
	pc.DateOfService,		
from   
	Childmaster cm,
	pclaim pc,   
	pclaimaux pca	
where  
	pca.childid = cm.childstateid 
	and pc.id = pca.pclaimid ) MainQuery, 

(SELECT TOP 1 '1' as sublink, pcr.specialtyid , sp.Description
         FROM          
   ProviderAccount pa,
   ProviderPayee pp,
   Provider pr,
   ProviderCredential pcr,
   Specialty sp
         WHERE      
   pca.ProviderID = PP.TaxID 
   and right(pca.ProviderSuffix,4) = substring(pa.PayeeSeq,1,4)
   and PP.ID = PA.PayeeID 
   and PA.ProviderID = PR.ID     
   and pcr.ProviderID = PR.id
   and pcr.SpecialtyID = sp.id) SubQuery

where Mainquery.mainlink = Subquery.sublink

order by 
	ChildStateID, 
	DateOfService

The objective:
1. Run both query as both subquerries.
2. Create an artificial key that will join the 2.
3. Join them using where (you can also use INNER JOIN

Good luck.

-- CK