Solved

Sql Syntax Help

Posted on 2008-06-17
7
223 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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 8 50
Help Parsing a String with SQL Syntax 23 45
Sql Server group by 10 30
Present Absent from working date rage 11 21
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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. …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 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