Solved

Sql Syntax Help

Posted on 2008-06-17
7
209 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now