Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Select empty rows in joined count

Posted on 2005-05-03
6
Medium Priority
?
246 Views
Last Modified: 2010-03-19
Hi,
I have the following query:
SELECT     COUNT(dbo.Message.TerminalSubscriptionID) AS Expr1, ST_Terminal_CommonName_1.CommonName
FROM         dbo.Message INNER JOIN
                      dbo.ST_Terminal_CommonName ST_Terminal_CommonName_1 ON
                      dbo.Message.TerminalSubscriptionID = ST_Terminal_CommonName_1.TerminalID
WHERE     (dbo.Message.ReceivedTimestamp BETWEEN '2005-05-01' AND GETDATE())
GROUP BY ST_Terminal_CommonName_1.CommonName

It counts the number of message sent by all terminals in the st_terminal_commonname table.
It does that fine, but if there are no messages in the dbo.Messages table, then the terminal is ignored, how can I make it so that the terminals with a 0 count (they will also not have a date in the dbo.Messages table) show in the results.
Thanks!
D.
 
0
Comment
Question by:maunded
  • 3
  • 3
6 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13922261
Try this:

SELECT     COUNT(dbo.Message.TerminalSubscriptionID) AS Expr1, ST_Terminal_CommonName_1.CommonName
FROM        dbo.ST_Terminal_CommonName ST_Terminal_CommonName_1
                         LEFT OUTER JOIN dbo.Message
                              ON dbo.Message.TerminalSubscriptionID = ST_Terminal_CommonName_1.TerminalID
WHERE     (dbo.Message.ReceivedTimestamp BETWEEN '2005-05-01' AND GETDATE())
GROUP BY ST_Terminal_CommonName_1.CommonName
0
 
LVL 1

Author Comment

by:maunded
ID: 13922286
That still dosent return a 0 value for the terminals with nothing in the Messages table
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13922291
How about this:

SELECT     COUNT(dbo.Message.TerminalSubscriptionID) AS Expr1, ST_Terminal_CommonName_1.CommonName
FROM        dbo.ST_Terminal_CommonName ST_Terminal_CommonName_1
                         LEFT OUTER JOIN dbo.Message
                              ON dbo.Message.TerminalSubscriptionID = ST_Terminal_CommonName_1.TerminalID AND
                                   dbo.Message.ReceivedTimestamp BETWEEN '2005-05-01' AND GETDATE()
GROUP BY ST_Terminal_CommonName_1.CommonName
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:maunded
ID: 13922313
Yup, thats got it...can you explain breifly what you changed?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13922350
From your original code, I used LEFT OUTER JOIN so that all rows from the table on the left will be in the output.  Aside from the LEFT OUTER JOIN, I interchanged your tables so that all rows from the dbo.ST_Terminal_CommonName ST_Terminal_CommonName_1 table will be in the output.  I could have used RIGHT OUTER JOIN that will produce the same result but LEFT OUTER JOIN is always the preferred option.

As to the second change that I did, I put your WHERE clause inside the JOIN.  With the WHERE clause, SQL will first join the 2 tables then once joined, it will perform the filtering of the rows based on the WHERE clause.  With this filtering, most of the records that were previously joined will be removed from the GROUPing.  Putting it inside the JOIN condition, SQL will first filter the rows from the second table then perform the join.

So the difference between the 2 is, with the WHERE, the tables are joined first then filtered while with the JOIN condition, the tables are filtered first then joined.

Hope this helps.
0
 
LVL 1

Author Comment

by:maunded
ID: 13922443
Brilliant!  Thanks very much!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

571 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