Solved

Need help with another query

Posted on 2017-04-14
10
57 Views
Last Modified: 2017-04-15
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
Comment
Question by:RadhaKrishnaKiJaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 42093548
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 42093551
OK you are using SQL Server but how are you getting the data, Linq to SQL, Entity Framework, or standard ADO.Net?
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 42093562
Standard ADO.Net
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:RadhaKrishnaKiJaya
ID: 42093635
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
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 42093641
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
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 42093666
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
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 42093668
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
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 42093737
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 42093816
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
 

Author Closing Comment

by:RadhaKrishnaKiJaya
ID: 42094409
Thanks a lot for your help.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question