MySQL Query

[seamus colgan]

0 down vote favorite


I need some support with a query. Basically I have a database for a Walks company. The tables are as follows, Guides - Walks - Participants - Programme - Booking and GuideWalk, which is a linking table.

I need to do a query that would give output data that would tell me for each walk, how many places there are, how many have booked and how many are available.

Any help would be appreciated.

The tables are as follows

CREATE TABLE `Participant` (
`part_id` int(11) NOT NULL AUTO_INCREMENT,
`part_sname` varchar(20) NOT NULL,
`part_fname` varchar(15) NOT NULL,
`part_phone` varchar(20) DEFAULT NULL,
`part_email` varchar(20) DEFAULT NULL,
`part_status` varchar(45) NOT NULL,
PRIMARY KEY (`part_id`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=latin1;

CREATE TABLE `Booking` (
`bookprog_id` int(11) NOT NULL AUTO_INCREMENT,
`part_id` int(11) NOT NULL,
PRIMARY KEY (`bookprog_id`,`part_id`),
KEY `part_id_idx` (`part_id`),
CONSTRAINT `bookprog` FOREIGN KEY (`bookprog_id`) REFERENCES `Programme` (`prog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `part_id` FOREIGN KEY (`part_id`) REFERENCES `Participant` (`part_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

CREATE TABLE `Guide` (
`guide_id` int(11) NOT NULL AUTO_INCREMENT,
`guide_fname` varchar(20) NOT NULL,
`guide_sname` varchar(20) NOT NULL,
`guide_address1` varchar(20) NOT NULL,
`guide_address2` varchar(20) DEFAULT NULL,
`guide_town` varchar(20) NOT NULL,
`guide_pcode` varchar(15) NOT NULL,
`guide_email` varchar(20) NOT NULL,
`guide_mphone` varchar(15) NOT NULL,
PRIMARY KEY (`guide_id`)
) ENGINE=InnoDB AUTO_INCREMENT=274 DEFAULT CHARSET=latin1;

CREATE TABLE `GuideWalk` (
`guide_id` int(11) NOT NULL,
`prog_id` int(11) NOT NULL,
PRIMARY KEY (`guide_id`,`prog_id`),
KEY `prog_id_idx` (`prog_id`),
CONSTRAINT `prog_id` FOREIGN KEY (`prog_id`) REFERENCES `Programme` (`prog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `guide_id` FOREIGN KEY (`guide_id`) REFERENCES `Guide` (`guide_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Programme` (
`prog_id` int(11) NOT NULL,
`pwalk_id` int(11) NOT NULL,
`prog_date` date NOT NULL,
`prog_stime` varchar(9) NOT NULL,
`prog_max` int(11) NOT NULL,
PRIMARY KEY (`prog_id`),
KEY `pwalk_id_idx` (`pwalk_id`),
CONSTRAINT `pwalk_id` FOREIGN KEY (`pwalk_id`) REFERENCES `Walk` (`walk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Walk` (
`walk_id` int(11) NOT NULL AUTO_INCREMENT,
`walk_title` varchar(15) NOT NULL,
`walk_dur` int(11) NOT NULL,
`walk_spoint` varchar(20) NOT NULL,
`walk_fpoint` varchar(20) NOT NULL,
`walk_desc` text NOT NULL,
`walkleader_1` varchar(20) NOT NULL,
`walkleader_2` varchar(20) DEFAULT NULL,
PRIMARY KEY (`walk_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

I have managed to output every day and how many places are available with this query

SELECT prog_max AS 'Places', prog_date AS 'Day' FROM Programme WHERE prog_date BETWEEN '2014-07-20' AND '2014-08-03'

I really haven't a clue on how to progress from here