Solved

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

Posted on 2013-10-29
4
222 Views
Last Modified: 2013-10-30
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
Comment
Question by:rjorge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Expert Comment

by:Barry62
ID: 39610638
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
 

Author Comment

by:rjorge
ID: 39610661
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
 
LVL 15

Accepted Solution

by:
DonConsolio earned 500 total points
ID: 39610694
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
 
LVL 8

Expert Comment

by:Barry62
ID: 39611465
Don is correct.  Use a left (or outer) join.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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