grogo21
asked on
Sql Syntax Help
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!
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(*)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(*)
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(*)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks! Would any one of these result in better performance over the others?
Open in new window