• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

How to retrieve query results when count() = 0 ?

Greetings,
I have the following (similar) structure :

create table players (
  ownerid int,
   name varchar(10)
)

create table games (
  gameid int,
  name varchar(255)
)

create table gameowner (
  ownerid int,
  gameid int
)

I want to make a query where I can list all the owners with less than 'x' games, including those that have none. I am having some problems with the second part. would greatly appreciate your help.

r
0
rjorge
Asked:
rjorge
  • 2
1 Solution
 
Barry62Commented:
select players.name,count(gameowner.gameid) as games
from players
join gameowner on players.ownerid = gameowner.ownerid
where games < 5
group by players.name,games
0
 
rjorgeAuthor Commented:
Hi Barry,
I wonder whether there might be a problem when the result of the join is a null line, because there is no line matching "join gameowner on players.ownerid = gameowner.ownerid", (e.g. there is an id on players for which there is no entry on gameowner table). As I see it may result on on a empty/null set. I still want to see those entries. See example output below.

name | games
john     1
jack      0
sam     2
0
 
DonConsolioCommented:
use a  left join on players and gameowner to find all from players and matching number of games.

use a subquery to restrict the result to  owners with less than 'x' (2 in this example)  games.

 select * from ( select players.ownerid , name, count(gameid) as numgames from players left join gameowner on players.ownerid  = gameowner.ownerid group by  players.ownerid ) as tsub where numgames < 2;

Open in new window

0
 
Barry62Commented:
Don is correct.  Use a left (or outer) join.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now