Join multiple tables

Hello all,

I am trying to join multiple tables which relate based on some columns however to get the reference I first have to get the results of another table I am not sure if I should create an easier reference column or I can run a query in the following way

Play
ID, Name, Etc.

Function
ID, Play.ID, Location.ID

Location
ID, State

Basically in the query I have the State’s name, for instance New York, I don’t need the functions here but filter the plays based on their location, if I user selected New York the query should only return the plays that are in New York based on the Location table, here is my query but is not returning the right results

SELECT DISTINCT play . * , company.name AS company, location.name AS locationname, location.id AS locationid
FROM  `theater_plays` AS play
LEFT JOIN  `theater_companies` AS company ON company.id = play.category
LEFT JOIN  `theater_locations` AS location ON location.astate =  'New York'
LEFT JOIN  `theater_functions` AS 
FUNCTION ON function.place = location.id
WHERE play.state =1
AND play.is_course =0
ORDER BY play.id DESC 
LIMIT 0 , 30

Well, not sure why you’re using LEFT JOINS unless you want all the records from threater_plays, it doesn’t matter if there’s any record related to it in any other table.

The query is definately wrong. location.astate doesn’t indicate which relationship it has and with table is related… location.astate should belong in the WHERE

That’s for a starters.

I modified the query as per your recommendation however I am still getting the same results

SELECT DISTINCT play.*, company.name as company, location.name
FROM `theater_plays` AS play
JOIN `theater_companies` as company
ON company.id = play.category
JOIN `theater_locations` as location
JOIN `theater_functions` as function
WHERE
location.astate = 'New York'
AND function.place = location.id
ORDER BY play.id DESC

Just for your information
location.asatate is a State such as New York
play.state is whether the play is publicly available or not
function.place is an id reference to the locations table

Just note that locations table may have many locations with the “New York” State

[quote=“tlacaelelrl, post:1, topic:104685, full:true”]
Play
ID, Name, Etc.

Function
ID, Play.ID, Location.ID

Location
ID, State[/quote]this information was unhelpful

here’s my best guess at what you want –

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

Thank you for all your help so far.

I am still getting mixed results where all the plays are matched against one single locality and all plays are still loading even if they are part of a different state

Let me try to better explain

I have multiple plays, with the following format

id: (the unique id)
name:  (the name of the play)
category: (fk to company)

Then the companies table looks like this

id: (the unique id)
name:  (the name of the company)

Up to here all works fine, I am able to retrieve the plays based on the company they belong to

Then I have the functions, all companies can be based anywhere however they may perform a function on any city, state, etc however if I get the filter by state then I can figure the rest out as it will be similar, so the functions table and locations table look like this

Functions
id: (the unique id)
place: (fk for location)
play: (fk for play)

Locations
id: (the unique id)
astate: (the name of the state)

Basically my only reference to those plays is the States’ names in the locations’ table, so I should be able to get all different locations belonging to “New York” then based on those locations filter out the functions that are part of those locations then since the functions have reference to the plays get all the plays on the list without repeating the plays

Here is a sample data

Play
id, name, category
1, play1, 1
2, play2, 3
3, play3, 3

Companies
id, name
1, company1
2, company2
3, company3

Functions
id, place, play
1, 1, 1
2, 1, 2
3, 2, 1

Locations
id, astate
1, 'New York'
2, 'Maryland'
3, 'California'

So if I were to run a query for New York I should get a result like this

Plays filtered
id, name, category, company
1, play1, 1, company1
2, play2, 3, company3

Because the id of New York is 1 so functions with ids (1, 2) are selected, functions.place make reference to locations, based on that we see that plays (1, 2) are part of those functions

Not sure if I am making much sense, let me know if I can explain it better

[quote=“tlacaelelrl, post:5, topic:104685, full:true”]Not sure if I am making much sense, let me know if I can explain it better
[/quote]yes, you can do better

do an actual SHOW CREATE TABLE for each table that you want to describe to us

then provide some INSERT statements that populate those tables (these are easily obtainable from a dump) – not too many, just enough for a comprehensive test case

then show your actual current query, along with the actual results that it produces, and describe what’s wrong with those results so that we can actually follow along and test alternatives ourselves on your data

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 fromtheater_functionsFiltering that selection with only unique values ontheater_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

This is the last query I ran

SELECT DISTINCT
play.id as pid, play.name as pname, company.name as cname, location.name as lname
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
AND location.astate = 'Chiapas'
ORDER BY play.id DESC

I tried adding also to the where clause but I am still not getting the correct results, it seems to me that I am getting a randomly picked location

I think I was going wrong about it, I haven’t fully tested it but I think i got it working with this query

SELECT 
location.name as lname,
function.id as fid,
play.name as pname,
play.id as pid
FROM `theater_locations` AS location
JOIN `theater_functions` as function
ON function.place = location.id
JOIN `theater_plays` as play
ON function.play = play.id
WHERE location.astate = 'Chiapas'

Let me know if I can do it better, I will be testing it in case something is not working right, thank you for all the help

[edit]
The results were fine except that I was getting the same play multiple times, adding GROUP BY play.id solved the issue

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.