Proper Syntax for multiple INNER JOIN commands

Databases
1

I’m getting this error message when I try to load my page:

“Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM titles INNER JOIN premieres ON dateid = premieres.id’ at line 3”

Here is my code:

try
{
  $sql = 'SELECT titles.id, Title, name, directors.link, dateid
      FROM titles INNER JOIN directors ON directorid = directors.id
      FROM titles INNER JOIN premieres ON dateid = premieres.id';
  $result = $pdo->query($sql);

Please tell me what I’m doing wrong.

2

You have two FROM and ON clauses and you should only have one of each.

FROM titles INNER JOIN directors INNER JOIN premieres ON directorid = directors.id AND dateid = premieres.id

3

Thank you Stephen!

4

correction: you have two FROMs and need only one, but you do need two ONs, otherwise you’ll get a cartesian crossproduct on the first join and have to filter most of them out

SELECT titles.id
     , titles.Title
     , titles.name
     , directors.link
     , titles.dateid  
  FROM titles 
INNER 
  JOIN directors 
    ON directors.id = titles.directorid 
INNER 
  JOIN premieres 
    ON premieres.id = titles.dateid

also, please note, it’s important that if your query has more than one table that you must qualify each column with the table it belongs to (see SELECT clause)

5

Thank you r937. I have a related question: I want to add links to Title, name, and format. I was able to add links to all three but when I click on them they only open the page linked to format when I want the links for Title and name to point to separate pages. Can you tell me where my error is? Here is part of the code for index.php:

try
{
  $sql = 'SELECT titles.id, 
  titles.Title, 
  directors.name,
  titles.link, 
  directors.link, 
  premieres.date, 
  availability.format, 
  availabilitylinks.link 
  FROM titles 
  INNER JOIN directors 
  ON directorid = directors.id
  INNER JOIN premieres 
  ON dateid = premieres.id
  INNER JOIN availability 
  ON availabilityid = availability.id
  INNER JOIN availabilitylinks
  ON availabilitylinkid = availabilitylinks.id';
  $result = $pdo->query($sql);
}
catch (PDOException $e)
{
  $error = 'Error fetching titles: ' . $e->getMessage();
  include 'error.html.php';
  exit();
}

foreach ($result as $row)
{
$titles[] = array(
'id' => $row['id'],
'text' => $row['Title'],
'titles.link' => $row['link'],
'name' => $row['name'],
'directors.link' => $row['link'],
'date' => $row['date'],
'format' => $row['format'],
'availabilitylinks.link' => $row['link']   
); 

}

include 'films.html.php';

And here is part of films.html.php:

<?php foreach ($titles as $film): ?>
      <form action="?deletefilm" method="post">
        <blockquote>
          <p>
            <a href="<?php
            echo htmlspecialchars($film['titles.link'], ENT_QUOTES,
                'UTF-8'); ?>"><?php
            echo htmlspecialchars($film['text'], ENT_QUOTES, 'UTF-8'); ?></a>
            <input type="hidden" name="id" value="<?php echo $film['id']; ?>">
            <input type="submit" value="Delete">
            (by <a href="<?php
            echo htmlspecialchars($film['directors.link'], ENT_QUOTES,
                'UTF-8'); ?>"><?php
            echo htmlspecialchars($film['name'], ENT_QUOTES,
                'UTF-8'); ?></a>) &nbsp; Premiered <?php
            echo htmlspecialchars($film['date'], ENT_QUOTES,
                'UTF-8'); ?> &nbsp; 
                <a href="<?php
            echo htmlspecialchars($film['availabilitylinks.link'], ENT_QUOTES,
                'UTF-8'); ?>"><?php
            echo htmlspecialchars($film['format'], ENT_QUOTES,
                'UTF-8'); ?></a>
          </p>
6

not me, sorry, i don’t do php

i’ve flagged this thread to be moved to the php forum

p.s. did you understand my comment about qualifying all column names with their table names? you should do this in the ON clauses too

7

Thank you. I thought I understood. Do I need to qualify on both sides of the = sign? e.g.

ON premieres.dateid = premieres.id

instead of

ON dateid = premieres.id

?

8

“need” is a very strong word

technically, you don’t need to qualify any columns that are unique across all tables in the query

but from a practical standpoint, it is something that you should do for clarity

9

Thank you!

10

@nerves ; @r937 ; I’ve copied the one post that seems to be related to the PHP script instead of SQL to the PHP forum. See: http://www.sitepoint.com/forums/showthread.php?1174822-Help-looping-through-results-and-building-links

I’ve left everything here in the Database forum, as there was definitely good techniques and help provided in that area.

Thanks

11

Thank YOU, sir!