Subquery (Correlated) Syntax 1

[AlanJordan]

I have this query, which works:

SELECT [Station Number] AS Station_Number, [Station Name] AS Station_Name, [Station City] FROM Stations

I want to add the count of the number of records in another table to the query. This SQL works for that purpose:

SELECT     COUNT(ComplaintNumber) AS [No Of Complaints]
FROM dbo.Complaints2 GROUP BY [Station Number]

I think the answer is to build a subquery. Can you advise me if I'm right, and the correct syntax?

This is my best shot at it, and it fails by saying that Count is not a valid object. Then SQL Server adds a CROSS JOIN in to the code.

SELECT     dbo.Stations.[Station Number], dbo.Stations.[Station Name], dbo.Stations.[Station City]
FROM         dbo.Complaints2 , dbo.Count(dbo.Complaints2.ComplaintNumber) AS [Num of Complaints] LEFT OUTER JOIN
                      dbo.Stations ON dbo.Complaints2.[Station Number] = dbo.Stations.[Station Number]
GROUP BY Complaints.ComplaintNumber, dbo.Stations.[Station Number], dbo.Stations.[Station Name], dbo.Stations.[Station City]

I think I need to use a where clause and set up a correlated subquery, but I'm having difficulty with the syntax.

Thanks, in advance.

 

[PWise]

SELECT [Station Number] AS Station_Number, [Station Name] AS Station_Name, [Station City] ,isnull([No Of Complaints],0)
FROM Stations
left join (SELECT COUNT(ComplaintNumber) AS [No Of Complaints],[Station Number]
FROM dbo.Complaints2 GROUP BY [Station Number])Complaints
on Complaints.[Station Number]=Stations.[Station Number]

 

[AlanJordan]

Excellent. :-D Thank you. I never thought of using the IsNull([No of Complaints],0) function. In fact, I didn't know it existed. It reminds me of the IIF function in VB and Access.

BTW, Could you tell me when it would be appropriate to use a WHERE clause for a subquery?

Here is the final code that I used. It only has a cosmetic tweak.

SELECT Stations.[Station Number] AS Station_Number, Stations.[Station Name] AS Station_Name, Stations.[Station City], ISNULL(Complaints.[No Of Complaints], 0) AS No_Of_Complaints FROM Stations LEFT OUTER JOIN (SELECT COUNT(ComplaintNumber) AS [No Of Complaints], [Station Number] FROM Complaints2 GROUP BY [Station Number]) AS Complaints ON Complaints.[Station Number] = Stations.[Station Number]

 

[PWise]

It reminds me of the IIF function in VB and Access.

It is the sister of the NZ function in access and vba

Could you tell me when it would be appropriate to use a WHERE clause for a subquery?

Anytime that you would use it in the top query