Can't get the right query

[TOA]

Hey guys. I'm hoping someone can help me out. I don't know if I've been looking at this problem too long, having a brain fart or what but I can't seem to figure it out.

 

I'm creating a simple game for work. Users do things for points, and whoever gets the most points at the end of a time period wins. They're organized into teams and there are 'prizes' for both individual and team high scores. All that works fine. What I'm trying to do now is show a leaderboard (sounds simple right? ).

 

I have it working using two queries, but then I have to do a bunch of stuff to it like custom sorting and rearranging to get proper order. I know I can get it using one query which would order it for me too, but I can't get the query correct.

 

What's happening is that it will ignore any teams that have no points (or rather a null total)

a. why is that?

b. how do I get it to show all the teams, even with a null total?

 

This is what I've got:

SELECT `bg_teams`.tid, `bg_teams`.name, SUM(`bg_points`.points) AS total
FROM `bg_teams`
INNER JOIN `bg_roster` ON `bg_teams`.tid=`bg_roster`.tid
INNER JOIN `bg_points` ON `bg_roster`.uid=`bg_points`.uid
INNER JOIN `bg_participants` ON `bg_teams`.pid=`bg_participants`.pid 
WHERE CURRENT_DATE() BETWEEN start AND end

 

Here's the table structure for that:

CREATE TABLE IF NOT EXISTS `bg_teams` (
  `tid` int(11) NOT NULL auto_increment,
  `name` varchar(255) collate utf8_unicode_ci NOT NULL,
  `pid` int(11) NOT NULL,
  PRIMARY KEY  (`tid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

CREATE TABLE IF NOT EXISTS `bg_roster` (
  `rid` int(11) NOT NULL auto_increment,
  `tid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  PRIMARY KEY  (`rid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;

CREATE TABLE IF NOT EXISTS `bg_points` (
  `pid` int(11) NOT NULL auto_increment,
  `uid` int(11) NOT NULL,
  `points` varchar(255) collate utf8_unicode_ci NOT NULL,
  `stamp` datetime NOT NULL,
  PRIMARY KEY  (`pid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=25 ;

CREATE TABLE IF NOT EXISTS `bg_participants` (
  `pid` int(11) NOT NULL auto_increment,
  `cid` int(11) NOT NULL,
  `start` date NOT NULL,
  `end` date NOT NULL,
  PRIMARY KEY  (`pid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

 

Thanks for checking this out.

[Jessica]

You'd need to do a LEFT JOIN rather than INNER if you want rows that don't have a matching value (IE, NULL) to show up.

[TOA]

You'd need to do a LEFT JOIN rather than INNER if you want rows that don't have a matching value (IE, NULL) to show up.

 

Thanks for the suggestion.

 

I thought of that and tried that too, just a minute ago. It either returns the same results or incorrect results, the incorrect results being the teams with null points are returned with the point values of the teams that do have points, which are left out. Depends on where I use LEFT instead. If I use LEFT on all of them, it returns the same results as the original query.

 

Hope that makes sense.

 

 

[Barand]

When using LEFT JOIN the Where condition needs to in the join condition

 

LEFT JOIN `bg_participants` ON `bg_teams`.pid=`bg_participants`.pid  AND CURRENT_DATE() BETWEEN start AND end

[TOA]

When using LEFT JOIN the Where condition needs to in the join condition

 

LEFT JOIN `bg_participants` ON `bg_teams`.pid=`bg_participants`.pid  AND CURRENT_DATE() BETWEEN start AND end

 

I'll give that a shot, thanks!

[TOA]

It still ignores NULL; I only get one result when I'm expecting at least two (that's how many there are in my test db - one with points and one with no points).

 

Am I doing something wrong here?? Here's my current query

SELECT `bg_teams`.tid, `bg_teams`.name, SUM(`bg_points`.points) AS total
FROM `bg_teams`
INNER JOIN `bg_roster` ON `bg_teams`.tid=`bg_roster`.tid
INNER JOIN `bg_points` ON `bg_roster`.uid=`bg_points`.uid
LEFT JOIN `bg_participants` ON `bg_teams`.pid=`bg_participants`.pid AND CURRENT_DATE() BETWEEN start AND end

 

Using all LEFT joins makes no difference, I still only get the one result.

 

Thanks for the help.

[Jessica]

INNER JOIN `bg_points` ON `bg_roster`.uid=`bg_points`.uid

 

THIS  one needs to be a LEFT join. You said one row has no matching points value.

[TOA]

You said one row has no matching points value.

 

That is correct.

 

INNER JOIN `bg_points` ON `bg_roster`.uid=`bg_points`.uid

 

THIS  one needs to be a LEFT join.

 

Using this query gets me an incorrect result. It returns the team that has empty points but it returns the other teams points with it.

SELECT `bg_teams`.tid, `bg_teams`.name, SUM(`bg_points`.points) AS total
FROM `bg_teams`
INNER JOIN `bg_roster` ON `bg_teams`.tid=`bg_roster`.tid
LEFT JOIN `bg_points` ON `bg_roster`.uid=`bg_points`.uid
INNER JOIN `bg_participants` ON `bg_teams`.pid=`bg_participants`.pid
WHERE CURRENT_DATE() BETWEEN start AND end

Ie:

Team Two - 16

 

This is what I'm trying to get as a result

Team One - 16

Team Two - null

 

Thanks for the help, I appreciate it.

[Jessica]

Can you post a dump of the data?

[TOA]

Can you post a dump of the data?

 

Sure thing...here it is.

--
-- Table structure for table `bg_participants`
--

CREATE TABLE IF NOT EXISTS `bg_participants` (
  `pid` int(11) NOT NULL auto_increment,
  `cid` int(11) NOT NULL,
  `start` date NOT NULL,
  `end` date NOT NULL,
  PRIMARY KEY  (`pid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

--
-- Dumping data for table `bg_participants`
--

INSERT INTO `bg_participants` (`pid`, `cid`, `start`, `end`) VALUES
(2, 5, '2012-08-26', '2012-09-30');

----------------------------------------------------------------------

--
-- Table structure for table `bg_points`
--

CREATE TABLE IF NOT EXISTS `bg_points` (
  `pid` int(11) NOT NULL auto_increment,
  `uid` int(11) NOT NULL,
  `points` varchar(255) collate utf8_unicode_ci NOT NULL,
  `stamp` datetime NOT NULL,
  PRIMARY KEY  (`pid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=27 ;

--
-- Dumping data for table `bg_points`
--

INSERT INTO `bg_points` (`pid`, `uid`, `points`, `stamp`) VALUES
(1, 1, '1', '2012-09-05 15:29:02'),
(2, 1, '1', '2012-09-05 15:24:34'),
(3, 1, '1', '2012-09-05 15:27:27'),
(4, 1, '1', '2012-09-05 15:27:38'),
(5, 1, '1', '2012-09-05 15:28:45'),
(6, 1, '1', '2012-09-05 15:41:00'),
(7, 1, '1', '2012-09-05 16:31:03'),
(8, 1, '1', '2012-09-05 16:34:35'),
(22, 1, '1', '2012-09-06 10:49:09'),
(21, 1, '1', '2012-09-06 10:48:12'),
(20, 1, '1', '2012-09-06 10:47:12'),
(19, 1, '1', '2012-09-06 10:45:29'),
(18, 1, '1', '2012-09-06 10:44:27'),
(17, 1, '1', '2012-09-06 10:23:41'),
(23, 1, '1', '2012-09-06 10:49:30'),
(24, 1, '1', '2012-09-06 13:16:00'),
(25, 1, '1', '2012-09-10 09:53:54'),
(26, 1, '1', '2012-09-10 10:14:09');


----------------------------------------------------------------------


--
-- Table structure for table `bg_roster`
--

CREATE TABLE IF NOT EXISTS `bg_roster` (
  `rid` int(11) NOT NULL auto_increment,
  `tid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  PRIMARY KEY  (`rid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;

--
-- Dumping data for table `bg_roster`
--

INSERT INTO `bg_roster` (`rid`, `tid`, `uid`) VALUES
(4, 3, 2),
(3, 2, 1);


----------------------------------------------------------------------


--
-- Table structure for table `bg_teams`
--

CREATE TABLE IF NOT EXISTS `bg_teams` (
  `tid` int(11) NOT NULL auto_increment,
  `name` varchar(255) collate utf8_unicode_ci NOT NULL,
  `pid` int(11) NOT NULL,
  PRIMARY KEY  (`tid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

--
-- Dumping data for table `bg_teams`
--

INSERT INTO `bg_teams` (`tid`, `name`, `pid`) VALUES
(2, 'Fake Team', 2),
(3, 'Team B', 2);

 

Thanks

[Barand]

When selecting columns and using an aggregation function, like SUM, you need a GROUP BY clause

 

SELECT `bg_teams`.tid, `bg_teams`.name, SUM(`bg_points`.points) AS total
FROM `bg_teams`
LEFT JOIN `bg_roster` ON `bg_teams`.tid=`bg_roster`.tid
LEFT JOIN `bg_points` ON `bg_roster`.uid=`bg_points`.uid
LEFT JOIN `bg_participants` ON `bg_teams`.pid=`bg_participants`.pid 
AND CURDATE() BETWEEN start AND end
GROUP BY `bg_teams`.tid;

[TOA]

When selecting columns and using an aggregation function, like SUM, you need a GROUP BY clause

 

That worked brilliantly.

 

Thank you very much!