[SOLVED] Joins

[padams]

I'm running a query that combines results from three tables, including counting and grouping data. It has player details, tries scored and games played. Currently it returns the player names even if they have zero games played. Is it possible to modify the query so that if a player has zero games they do not appear in the results?

 

I'm using a left join, but I know that includes results even if there is nothing in one of the columns. Can I get around the problem by using a different join type? I've tried reading up on them and played around with some but can't get the result I want.

 

SELECT p.`playerID`, p.`playerFirstName`, p.`playerLastName`, IFNULL(g.gamescount,0) as gamescount, IFNULL(t.trycount,0) as trycount FROM players p LEFT JOIN (SELECT playerID, COUNT(*) as gamescount FROM games GROUP BY playerID) as g USING (playerID) LEFT JOIN (SELECT playerID, COUNT(*) as trycount FROM tries GROUP BY playerID) as t  USING (playerID) ORDER BY gamescount DESC

[Daniel0]

I don't think you need a join for that. Try this query:

 

SELECT p.playerID, p.playerFirstName, p.playerLastName,
       (SELECT COUNT(g.*) FROM games g WHERE g.playerID = p.playerID) gamescount,
       (SELECT COUNT(t.*) FROM tries t WHERE t.playerID = p.playerID) trycount
  FROM players p
  WHERE gamescount > 0
  ORDER BY gamescount DESC;

[padams]

Tried that and got the following error:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM games g WHERE g.playerID = p.playerID) gamescount,

      (SELECT COUNT' at line 2

[Daniel0]

Try just COUNT(*) in both instances.

[padams]

Tried:

SELECT p.playerID, p.playerFirstName, p.playerLastName,

      (SELECT COUNT(*) FROM games g WHERE g.playerID = p.playerID) gamescount,

      (SELECT COUNT(*) FROM tries t WHERE t.playerID = p.playerID) trycount

  FROM players p

  WHERE gamescount > 0

  ORDER BY gamescount DESC;

 

Got another error:  #1054 - Unknown column 'gamescount' in 'where clause'

[zenag]

(SELECT COUNT(g.*) FROM games g WHERE g.playerID = p.playerID)as  gamescount,(SELECT COUNT(t.*) FROM tries t WHERE t.playerID = p.playerID)as trycount

[padams]

Result was another error:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) FROM games g WHERE g . playerID = p . playerID ) as gamescount , ( SELECT CO' at line 1

[zenag]

use count(*) or count(g.fieldname)not as count(g.*)

[Barand]

Tried:

SELECT p.playerID, p.playerFirstName, p.playerLastName,

      (SELECT COUNT(*) FROM games g WHERE g.playerID = p.playerID) gamescount,

      (SELECT COUNT(*) FROM tries t WHERE t.playerID = p.playerID) trycount

  FROM players p

  WHERE gamescount > 0

  ORDER BY gamescount DESC;

 

Got another error:  #1054 - Unknown column 'gamescount' in 'where clause'

 

You can't use an alias in a WHERE clause since the alias is unknown when the table data is being filtered.

 

If you do an INNER JOIN withe games table you will only get those players who have at least one game

[padams]

Great, the inner join worked a treat. Thanks for the help.

 

SELECT p.`playerID`, p.`playerFirstName`, p.`playerLastName`, IFNULL(g.gamescount,0) as gamescount, IFNULL(t.trycount,0) as trycount FROM players p INNER JOIN (SELECT playerID, COUNT(*) as gamescount FROM games GROUP BY playerID) as g USING (playerID) LEFT JOIN (SELECT playerID, COUNT(*) as trycount FROM tries GROUP BY playerID) as t  USING (playerID) ORDER BY gamescount DESC