[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Need help with another query

Posted on 2017-04-14
10
Medium Priority
?
71 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 44

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 64

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 44

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 44

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

650 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