正确完成使用group by连接4个表

Question

I am new in using advanced SQL queries and I am struggling with one query.

I have booking system created in php and it is using 4 tables:

• site_days
• site_timeslots
• site_bookings
• site_teams

• each site_team is related to site_booking
• each site_booking is related to site_timeslot
• each site_timeslot is related to site_days

there can be more site_timeslots related to one site_day there can be more site_bookings related to one site_timeslot there can be more site_teams related to one site_bookings

you can create test tables with this sql:

-- Adminer 3.6.3 MySQL dump

SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = 'SYSTEM';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `site_bookings`;
CREATE TABLE `site_bookings` (
  `id` int(11) NOT NULL auto_increment,
  `timeslot_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `site_bookings` (`id`, `timeslot_id`) VALUES
(1, 6443);

DROP TABLE IF EXISTS `site_days`;
CREATE TABLE `site_days` (
  `id` int(11) NOT NULL auto_increment,
  `date` date NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `site_days` (`id`, `date`) VALUES
(85,    '2013-04-01'),
(92,    '2013-04-02');

DROP TABLE IF EXISTS `site_teams`;
CREATE TABLE `site_teams` (
  `id` int(11) NOT NULL auto_increment,
  `booking_id` int(11) NOT NULL,
  `name` varchar(100) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `site_teams` (`id`, `booking_id`, `name`) VALUES
(1, 1,  'Avengers'),
(2, 1,  'Big Five');

DROP TABLE IF EXISTS `site_timeslots`;
CREATE TABLE `site_timeslots` (
  `id` int(11) NOT NULL auto_increment,
  `day_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `starts` time NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7152 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `site_timeslots` (`id`, `day_id`, `date`, `starts`) VALUES
(6443,  85, '2013-04-01',   '08:00:00'),
(6444,  85, '2013-04-01',   '08:10:00'),
(7098,  92, '2013-04-02',   '08:00:00'),
(7099,  92, '2013-04-02',   '08:10:00');

As a result I want to get ALL timeslots of table site_timeslots with few additional info: - for each site_timeslot I want to know count of site_teams in all related bookings to that timeslot in total (for example if there are 2 site_bookings for that site_timeslot and each has 2 site_teams, then total count should be 4) and also count of related bookings.

I have tried this sql:

SELECT `site_teams`.`id` AS site_teams_id, `site_teams`.`name` AS site_teams_name, `site_teams`.`booking_id` AS site_teams_booking_id, `site_days`.`id` AS site_days_id, `site_days`.`date` AS site_days_date, `site_timeslots`.`id` AS site_timeslots_id, `site_timeslots`.`starts` AS site_timeslots_starts, `site_bookings`.`id` AS site_bookings_id, `site_bookings`.`timeslot_id` AS site_bookings_timeslot_id
FROM (`site_days`)
LEFT JOIN `site_timeslots` ON `site_timeslots`.`day_id` = `site_days`.`id`
LEFT JOIN `site_bookings` ON `site_bookings`.`timeslot_id` = `site_timeslots`.`id`
LEFT JOIN `site_teams` ON `site_teams`.`booking_id` = `site_bookings`.`id`
GROUP BY `site_teams`.`booking_id`

-> but i won't get timeslots which haven't got any site_bookings, please how I should alter this sql query to have in result:

1. site_timeslot per row
2. count of site_bookings related to that site_timeslot in new column 'count_of_site_bookings'
3. count of site_teams related to all site_bookings that are related to that site_timeslot in new column 'count_of_site_teams'

Answer 1

You can do this by LEFT JOINing starting on site_timeslots and then using COUNT on the 2 relevant fields to get the totals you are after

SELECT

  sti.*,
  COUNT(DISTINCT sb.id) AS count_of_site_bookings,
  COUNT(DISTINCT ste.id) AS count_of_site_teams

FROM site_timeslots sti

INNER JOIN site_days sd
  ON sd.id = sti.day_id


LEFT JOIN site_bookings sb
ON sb.timeslot_id = sti.id

LEFT JOIN site_teams ste
ON ste.booking_id = sb.id

GROUP BY sti.id

You can find this on SQL Fiddle http://sqlfiddle.com/#!2/1a253/2

I also did a previous version which used a subquery due to an incorrect assumption on my part, if you'd like to take a look at that for reference it's available also at http://sqlfiddle.com/#!2/9ccf2/10