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

x
?
Solved

Sql Syntax Help

Posted on 2008-06-20
2
Medium Priority
?
304 Views
Last Modified: 2010-03-20
Hello, please reffer to my question here:

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23494097.html

I chose to use:
select s.serverip, p.serverid, isnull(p.processCnt ,0) processCnt
from servers s left outer join
( Select serverid, count(1) processCnt from processqueue
Group by serverid) as p
on s.serverid = p.serverid And s.status = 100
and p.processCnt <= s.capacity
order by p.processCnt

But, I found an issue with it after I closed the question.  The problem is that p.serverid is returning null when p.processCnt  returns zero.  I need it to return the ServerId and not a null value. How can I fix this?

Thanks Very Much!
0
Comment
Question by:grogo21
2 Comments
 
LVL 2

Assisted Solution

by:jgoeders
jgoeders earned 400 total points
ID: 21835326
Hmmm....it seems that your sub-query p is counting records.  If the count is 0 that means there are no records.  How will it get a serverID out of no records?
0
 

Accepted Solution

by:
grogo21 earned 0 total points
ID: 21835586
Solution:

select s.serverip, s.serverid, count(*) as processes
from servers s left outer join processqueue p on s.serverid = p.serverid
where s.status = 100
group by s.serverid, p.serverid, s.capacity, s.serverip
having count(*) < s.capacity
order by count(*)

Thanks!
0

Featured Post

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!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

577 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