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-17
7
Medium Priority
?
258 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 400 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
Technology Partners: 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!

 
LVL 35

Accepted Solution

by:
Terry Woods earned 800 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 800 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.​
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

581 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