Nesting or working with Subqueries

Other SQL Server Topics
1

Hi - I've built a report in Report Writer and need to add a calculation. I've switched from working in the 'visual designer' to 'edit as text' area. I'm trying to 'nest' or add a subquery. Everything seems to work fine until I add the subquery.

Here's the initial query:
SELECT
Vendors.VendorID
,Vendors.VendorNO
,Vendors.AGENCY
,Vendors.PROVIDERTYPE
,Vendors.ACTIVE
,HISAddress.Street
,HISAddress.Street2
,HISAddress.City
,HISAddress.[State]
,HISAddress.ZipCode
,VendorCAPS.CAPID
,VendorCAPS.ContactedDate
,VendorCAPS.Status
,CASE WHEN ISNULL(VendorCAPS.Score2,'') = '' THEN '0' ELSE REPLACE(VendorCAPS.Score2, '%','')END +'%' Score2
,VendorCAPS.GenericLookup1
,VendorCAPS.GenericLookup2
,VendorCAPS.GenericLookup3
FROM
Vendors
INNER JOIN HISAddress
ON Vendors.VendorID = HISAddress.ChapterEntityID
INNER JOIN VendorCAPS
ON Vendors.VendorID = VendorCAPS.VendorID
INNER JOIN VendorCAPSItems
ON VendorCAPS.CAPID = VendorCAPSItems.CAPID
WHERE
HISAddress.AddressType = N'Contact'
AND VendorCAPS.ContactedDate >= @ContactedDate
AND VendorCAPS.ContactedDate <= @ContactedDate2
AND Vendors.ACTIVE IN (@ACTIVE)
AND Vendors.PROVIDERTYPE IN (@PROVIDERTYPE)
AND VendorCAPS.GenericLookup2 IN (@GenericLookup2)

I'm trying to incorporate this:
SELECT
vc.VendorID,
vc.CAPID As QIPID,
(SELECT COUNT(vci.ItemID)

FROM VendorCAPSItems vci WHERE vc.CAPID = vci.CAPID) AS NumberOfItems

FROM VendorCAPS vc
LEFT JOIN Vendors v ON vc.VendorID = v.VendorID
--WHERE vc.VendorID = @ProviderID

I'm stuck in a loop of syntax messages. Any suggestions on what I'm doing wrong?

2

This will run the select statement for every record it finds and slow it down. What are the error messages you are getting?

3 
SELECT
Vendors.VendorID
,Vendors.VendorNO
,Vendors.AGENCY
,Vendors.PROVIDERTYPE
,Vendors.ACTIVE
,HISAddress.Street
,HISAddress.Street2
,HISAddress.City
,HISAddress.[State]
,HISAddress.ZipCode
,VendorCAPS.CAPID
,VendorCAPS.ContactedDate
,VendorCAPS.Status
,CASE WHEN ISNULL(VendorCAPS.Score2,'') = '' THEN '0' ELSE REPLACE(VendorCAPS.Score2, '%','')END +'%' Score2
,VendorCAPS.GenericLookup1
,VendorCAPS.GenericLookup2
,VendorCAPS.GenericLookup3
,vci.ItemIDCount
FROM
Vendors
INNER JOIN HISAddress
ON Vendors.VendorID = HISAddress.ChapterEntityID
INNER JOIN VendorCAPS
ON Vendors.VendorID = VendorCAPS.VendorID
--INNER JOIN VendorCAPSItems
--ON VendorCAPS.CAPID = VendorCAPSItems.CAPID
INNER JOIN (
    SELECT CAPID, COUNT(ItemID) AS ItemIDCount
    FROM VendorCAPSItems
    GROUP BY CAPID
) AS vci 
ON vc.CAPID = vci.CAPID 
WHERE
HISAddress.AddressType = N'Contact'
AND VendorCAPS.ContactedDate >= @ContactedDate
AND VendorCAPS.ContactedDate <= @ContactedDate2
AND Vendors.ACTIVE IN (@ACTIVE)
AND Vendors.PROVIDERTYPE IN (@PROVIDERTYPE)
AND VendorCAPS.GenericLookup2 IN (@GenericLookup2)
4

Good morning. I was getting 'The multi-part identifier' .... could not be bound.' message yesterday. The message string listed out almost every field.

I've incorporated the changes ScottPletcher provided and now I'm down to just one message (yeah progress!).

Error details:
The multi-part identifier "vc.CAPID" could not be bound.

Error Number: 4104
Severity: 16
State: 1
Line Number: 33

Is there a resource I can download that can assist me with identifying what the 'line number' is? Someone mentioned 'Toad for SQL Server'.

Thanks for the assist.

5

Sorry, I used the wrong alias name:
...
ON VendorCAPS.CAPID = vci.CAPID
...

Btw, If you click on the error message in SSMS, it will take you to the line with the error.

1 Like
6

It worked!! Thank you, Thank you, Thank you! I will look into SSMS and add that to my SQL 'toolkit'.