• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

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.
0
RadhaKrishnaKiJaya
Asked:
RadhaKrishnaKiJaya
1 Solution
 
zephyr_hex (Megan)DeveloperCommented:
Depending on how many records you have in your db, this could be a simple subquery:

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

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.
0
 
Fernando SotoRetiredCommented:
OK you are using SQL Server but how are you getting the data, Linq to SQL, Entity Framework, or standard ADO.Net?
0
 
RadhaKrishnaKiJayaAuthor Commented:
Standard ADO.Net
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
RadhaKrishnaKiJayaAuthor Commented:
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.
0
 
zephyr_hex (Megan)DeveloperCommented:
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

Open in new window

0
 
RadhaKrishnaKiJayaAuthor Commented:
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."
0
 
zephyr_hex (Megan)DeveloperCommented:
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 

Open in new window

0
 
SharathData EngineerCommented:
SELECT l.*,r.Room
  FROM Location l
  JOIN (SELECT SchoolID, COUNT(*) Room FROM Rooms GROUP BY SchoolID) r
    ON l.LocationID = r.SchoolID

Open in new window

1
 
PortletPaulCommented:
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)
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

Open in new window

0
 
RadhaKrishnaKiJayaAuthor Commented:
Thanks a lot for your help.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now