RadhaKrishnaKiJaya
asked on
Need help with another query
Hi Experts,
I have the following data.
Location
--------------
LocationID SchoolName Address City State Zip
9 Northbrook 172 Church Northbrook IL 60078
10 GlenView
Rooms
------------
RoomID SchoolID RoomName
1 9 AYM
2 9 BL
3 9 KS
4 10 Room1
5 10 Room2
With the location details I want the room count for that location.
Output
------------
LocationID SchoolName Address City State Zip Room
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
9 Northbrook 172 Church Northbrook IL 60078 3
10 Glenview 2
Thanks in advance.
I have the following data.
Location
--------------
LocationID SchoolName Address City State Zip
9 Northbrook 172 Church Northbrook IL 60078
10 GlenView
Rooms
------------
RoomID SchoolID RoomName
1 9 AYM
2 9 BL
3 9 KS
4 10 Room1
5 10 Room2
With the location details I want the room count for that location.
Output
------------
LocationID SchoolName Address City State Zip Room
--------------------------
9 Northbrook 172 Church Northbrook IL 60078 3
10 Glenview 2
Thanks in advance.
OK you are using SQL Server but how are you getting the data, Linq to SQL, Entity Framework, or standard ADO.Net?
ASKER
Standard ADO.Net
ASKER
Hi,
I tried the query you have given. Slightly modified it. Because it was giving error.
select S.LocationID,S.CompanyID,
(SELECT count(R.SchoolID)
FROM [BeyondGreen].[dbo].[Rooms ] R
left outer join Schools on Schools .LocationID = R.SchoolID
group by R.SchoolID) from Schools S
But still I get the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Thanks in advance.
I tried the query you have given. Slightly modified it. Because it was giving error.
select S.LocationID,S.CompanyID,
(SELECT count(R.SchoolID)
FROM [BeyondGreen].[dbo].[Rooms
left outer join Schools on Schools .LocationID = R.SchoolID
group by R.SchoolID) from Schools S
But still I get the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Thanks in advance.
You shouldn't have to left join Schools in the subquery. A subquery joins to the main table. But I had an error in my initial query.
SELECT l.LocationId, l.SchoolName, l.Address, l.City, l.[State], l.Zip,
(SELECT COUNT(r.RoomID) FROM Rooms r WHERE r.SchoolId = l.LocationId GROUP BY r.RoomId, r.SchoolId) AS Room
FROM Location l //I was missing this part
ASKER
Still getting the same error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Sorry, my bad. You don't want to group by RoomId. Just by LocationId.
SELECT l.LocationId, l.SchoolName, l.Address, l.City, l.[State], l.Zip,
(SELECT COUNT(r.RoomID) FROM Rooms r WHERE r.SchoolId = l.LocationId GROUP BY r.SchoolId) AS Room
FROM Location l
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Joining a "derived table" which calculates the required counts, as proposed by Sharath, should be the most efficient approach.
Suggest you avoid "correlated subqueries" in a select clause as these can cause performance issues.
In MS SQL Server, if you really wanted a correlated subquery approach, use an APPLY operator (an OUTER APPLY is used in case there are no rows in the rooms table for a location)
Suggest you avoid "correlated subqueries" in a select clause as these can cause performance issues.
In MS SQL Server, if you really wanted a correlated subquery approach, use an APPLY operator (an OUTER APPLY is used in case there are no rows in the rooms table for a location)
SELECT
l.LocationId
, l.SchoolName
, l.Address
, l.City
, l.[State]
, l.Zip
, oa.room_count
FROM Location AS l
OUTER APPLY (
SELECT
COUNT(*) AS room_count
FROM Rooms r
WHERE r.SchoolId = l.LocationId
) oa
ASKER
Thanks a lot for your help.
Open in new window
You may want to re-think this structure if you have a very large number of records, as subqueries can slow down the performance when it's a large data set.