Ok so here is the table structure
--
-- Table structure for table `theater_companies`
--
CREATE TABLE IF NOT EXISTS `theater_companies` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`state` tinyint(1) NOT NULL DEFAULT '1',
`name` varchar(255) NOT NULL,
`category` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;
--
-- Dumping data for table `theater_companies`
--
INSERT INTO `theater_companies` (`id`, `state`, `name`, `category`) VALUES
(1, 1, 'Ven y Mira', ''),
(3, 1, 'Carapacho', ''),
(4, 1, 'El Club Minivenganzas', ''),
(5, 1, 'Crisol', ''),
(6, 1, 'Creación Teatral', '');
-- --------------------------------------------------------
--
-- Table structure for table `theater_functions`
--
CREATE TABLE IF NOT EXISTS `theater_functions` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`state` tinyint(1) NOT NULL DEFAULT '1',
`play` int(11) NOT NULL,
`date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`place` int(11) NOT NULL,
`timezone` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ;
--
-- Dumping data for table `theater_functions`
--
INSERT INTO `theater_functions` (`id`, `state`, `play`, `date`, `place`, `timezone`) VALUES
(2, 1, 1, '2011-04-01 01:06:00', 1, 'America/Mexico_City'),
(3, 1, 3, '2013-11-11 20:00:00', 3, 'America/Mexico_City'),
(4, 1, 1, '2013-11-11 18:15:00', 6, 'America/Mexico_City'),
(5, 1, 2, '2013-11-12 17:00:00', 6, 'America/Mexico_City'),
(6, 1, 5, '2013-11-12 18:00:00', 3, 'America/Mexico_City'),
(7, 1, 3, '2013-11-12 20:00:00', 3, 'America/Mexico_City'),
(8, 1, 3, '2013-11-13 18:00:00', 3, 'America/Mexico_City');
-- --------------------------------------------------------
--
-- Table structure for table `theater_locations`
--
CREATE TABLE IF NOT EXISTS `theater_locations` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`state` tinyint(1) NOT NULL DEFAULT '1',
`name` varchar(255) NOT NULL,
`country` varchar(255) NOT NULL,
`astate` varchar(255) NOT NULL,
`city` varchar(255) NOT NULL,
`town` varchar(255) NOT NULL,
`street` varchar(255) NOT NULL,
`number` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;
--
-- Dumping data for table `theater_locations`
--
INSERT INTO `theater_locations` (`id`, `state`, `name`, `country`, `astate`, `city`, `town`, `street`, `number`) VALUES
(1, 1, 'Museo del Ferrocarril Mexicano del Sur', 'Mexico', 'Oaxaca', 'Oaxaca', 'Del Ex-marquezado', 'Calzada Madero', '511'),
(3, 1, 'Teatro Juárez', 'México', 'Oaxaca', 'Oaxaca', 'RUTA INDEPENDENCIA', 'Av. Juárez', '703'),
(4, 1, 'Casa de la Cultura Oaxaqueña', 'México', 'Oaxaca', 'Oaxaca', 'S-1', ' González Ortega', '403'),
(5, 1, 'Teatro Macedonio Alcalá', 'México', 'Oaxaca', 'Oaxaca', 'Centro', 'Independencia ', '900'),
(6, 1, 'Centro Cultural Universitario de la Universidad Autónoma de Chiapas', 'México', 'Chiapas', 'San Cristobal de las Casas', 'Universitaria Uabjo', 'Av. Universidad', 'S/N');
-- --------------------------------------------------------
--
-- Table structure for table `theater_plays`
--
CREATE TABLE IF NOT EXISTS `theater_plays` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`state` tinyint(1) NOT NULL DEFAULT '1',
`name` varchar(255) NOT NULL,
`category` varchar(255) NOT NULL,
`is_course` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=30 ;
--
-- Dumping data for table `theater_plays`
--
INSERT INTO `theater_plays` (`id`, `state`, `name`, `category`, `is_course`) VALUES
(1, 1, 'Cancamusa', '1', 0),
(2, 1, 'Los Betta Splendens', '1', 0),
(24, 1, 'Seminario de teatro', '1', 1),
(25, 1, 'Veneno', '1', 0),
(3, 1, 'Las Muchachas', '1', 0),
(4, 1, 'Canibalismo escénico', '1', 0),
(5, 1, '5,7 Maggy', '1', 0);
As far as the query goes I tried the last one you provided me with
SELECT play.*
, company.name AS company
, loc.name AS locationname
, loc.id AS locationid
FROM location AS loc
INNER
JOIN theater_functions AS tfun
ON tfun.place = loc.id
INNER
JOIN theater_plays AS play
ON play.id = tfun.play_id
AND play.state = 1
AND play.is_course = 0
INNER
JOIN theater_companies AS company
ON company.id = play.category
WHERE loc.astate = 'New York'
ORDER
BY play.id DESC LIMIT 30
And also this is the one I was using
SELECT DISTINCT play.id, play.category, play.mainpicture, play.name, company.name location.name
FROM `theater_plays` AS play
JOIN `theater_companies` as company
ON company.id = play.category
JOIN `theater_locations` as location
ON location.astate = 'Chiapas'
JOIN `theater_functions` as function
ON function.place = location.id
WHERE play.state = 1
AND play.is_course = 0
ORDER BY play.id DESC
The way the query should work is that the user selects a State from the States’ list, in this case “Chiapas”
The I run a query on theater_locations
selecting based on the States’ name
theater_locations
.astate = ‘Chiapas’
So only row with id 6 is selected from theater_locations
Then based on that theater_functions
.play is selected based on theater_locations
.id = theater_functions
.placeSo only rows with ids 4, 5 are selected from
theater_functionsFiltering that selection with only unique values on
theater_functions`.play we then have the correct plays to be returned, in this case plays with ids 1, 2
With either of the previous queries my problem is that is returning all rows in the theater_plays table