Solved

Sql Syntax Help

Posted on 2008-06-20
2
295 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
[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
2 Comments
 
LVL 2

Assisted Solution

by:jgoeders
jgoeders earned 100 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 55
UPDATE JOIN multiple tables 5 23
Convert time stamp to date 2 57
Need to 'version' updates to a specific row in a MSSQL server 2 22
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

726 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