Solved

Need help with another query

Posted on 2017-04-14
10
53 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 48

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

734 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