Solved

Sql Syntax Help

Posted on 2008-06-17
7
227 Views
Last Modified: 2010-03-20
Hello how can i select all serverid in servers and get a count of each instance in processqueue.  The sql below returns no results if processqueue contains no records.  I would like it to return a count of zero if an serverid is not currently in processqueue. I need this to be as efficent as possible as it will execute several times a second.

select top 1 s.serverip, p.serverid
from servers s, processqueue p
where(s.serverid = p.serverid And s.status = 100)
group by s.serverid, p.serverid, s.capacity, s.serverip
having count(*) <= s.capacity
order by count(*)

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

Expert Comment

by:Terry Woods
ID: 21810117
Try this:
select top 1 s.serverip, p.serverid
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(*)

Open in new window

0
 
LVL 3

Assisted Solution

by:amritgill
amritgill earned 100 total points
ID: 21810124
use the isnull function to return the 0 value if it is null...shown below..

select top 1 s.isnull(serverip,0), isnull(p.serverid,0)
from servers s, processqueue p
where(s.serverid = p.serverid And s.status = 100)
group by s.serverid, p.serverid, s.capacity, s.serverip
having count(*) <= s.capacity
order by count(*)
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 21810127
Actually, this may suit your requirements better, though you probably could've worked it out:
select s.serverip, p.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(*)

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 35

Accepted Solution

by:
Terry Woods earned 200 total points
ID: 21810136
Correction, as you wouldn't have got a serverid when there were no processes:
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(*)

Open in new window

0
 
LVL 39

Expert Comment

by:appari
ID: 21810149
try this

select top 1 s.serverip, p.serverid
from servers s left outer join processqueue p
on s.serverid = p.serverid And s.status = 100
group by s.serverid, p.serverid, s.capacity, s.serverip
having count(*) <= s.capacity
order by count(*)
0
 
LVL 39

Assisted Solution

by:appari
appari earned 200 total points
ID: 21810164
or try this one

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
0
 

Author Comment

by:grogo21
ID: 21810304
Thanks! Would any one of these result in better performance over the others?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Please help with the below query - SQL Server 11 28
Email Notifications for SQL 2005 9 35
Get number of Files in Directory and Sub Directories 2 48
Query Syntax 17 42
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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