Select data the the ID exists on another table in SQL

Question

I have 2 tables, let says the first table named "Schedule" and the second named "Setting".

TABLE "SCHEDULE"

 ID    Name    StartTime    EndTime     Room  
---------------------------------------------
 111   AAAA    08:00        09:00       -
 112   BBBB    08:00        09:00       -
 113   CCCC    08:00        09:00       -
 114   DDDD    08:30        09:30       -
 115   EEEE    08:30        09:30       -   
 116   FFFF    08:30        09:30       -      

TABLE "SETTING"

 ID    Type    Room  
------------------------
 111     1     BPD01  
 112     2     BPR33  
 113     2     BPR33  
 114     2     BPR35  
 115     2     BPR33  

I want to get the result like:

 Room    StartTime  EndTime  Total Used   
---------------------------------------
 BPR33   08:00      09:00    2           
 BPR33   08:30      09:30    1           
 BPR35   08:30      09:30    1               

I have the code like:

SELECT ST.Room, S.StartTime, S.EndTime, COUNT(*) AS [Total Used]
FROM Schedule AS S CROSS JOIN Setting AS ST
WHERE (ID IN
  (SELECT ID FROM Setting
   WHERE (Type = '2')))
GROUP BY ST.Room, S.StartTime, S.EndTime

But the code resulted in showing all of the record in Setting with the counted value, the filter does not run properly.

How to do that?

Solution 1

I see a few potential issues here:

• Your WHERE ID IN (SELECT ID FROM Setting WHERE Type='2') looks like it could just be WHERE Type='2'
• What data type is your Type column? If it is INT, do WHERE Type = 2 as opposed to WHERE Type = '2' (which you'd use if Type was VARCHAR).
• You do not appear to have a JOIN condition linking your two tables together (e.g. WHERE S.ID = ST.ID)

All together now:

SELECT ST.Room, S.StartTime, S.EndTime, COUNT(*) AS [Total Used]
FROM Schedule AS S CROSS JOIN Setting AS ST
WHERE S.ID = ST.ID 
AND Type = 2
GROUP BY ST.Room, S.StartTime, S.EndTime

Solution 2

SELECT ST.Room, S.StartTime, S.EndTime, COUNT(1) AS [Total Used]
FROM Setting AS ST
LEFT JOIN Schedule AS S 
ON S.ID = ST.ID
WHERE ST.Type = '2'
GROUP BY ST.Room, S.StartTime, S.EndTime

This shows the ones that were not scheduled with a zero count. If you don't want that the join would change to:

SELECT ST.Room, S.StartTime, S.EndTime, COUNT(1) AS [Total Used]
FROM Setting AS ST
JOIN Schedule AS S 
ON S.ID = ST.ID
WHERE ST.Type = '2'
GROUP BY ST.Room, S.StartTime, S.EndTime

Comments

[marc_s]

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!

[mathematical.coffee]

Type is not the same as Total Used in this case, being the COUNT for each unique Room (where Type == 2).

[Greg]

I was thinking something similar, but I can't work out why you need the join to Schedule. What does it add to the query?

[mrjimoy_05]

@JBrooks: Thanks, I have figured out the problem :) . But why you stated Count(1) rather than Count(*)?

[mrjimoy_05]

Thanks, it's work :) . The data type for Type column is INT. And just like what you said, I forgot to have the join condition. :)

[JBrooks]

@mrjimoy_05 They result in the same and you should use Count(*) because of SQL-92. In the past Count(1) was a little more efficiency than Count(*) because it didn't have to hit the columns table.

[JBrooks]

Why a CROSS JOIN and WHERE instead of a JOIN and ON?

[mathematical.coffee]

Purely because that was what the OP used and I'm a MySQL person, and I wasn't sure if 'JOIN .. ON' (my preferred syntax) worked in SQL Server.