SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2013
    Posts
    35
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Proper Syntax for multiple INNER JOIN commands

    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:

    PHP 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. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,608
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    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
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2013
    Posts
    35
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    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
    Thank you Stephen!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by felgall View Post
    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
    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
    Code:
    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)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2013
    Posts
    35
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    Code:
    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)
    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:

    Code:
    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:

    Code:
    <?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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by nerves View Post
    Can you tell me where my error is?
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2013
    Posts
    35
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    Thank you. I thought I understood. Do I need to qualify on both sides of the = sign? e.g.
    Code:
    ON premieres.dateid = premieres.id
    instead of
    Code:
    ON dateid = premieres.id
    ?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by nerves View Post
    Do I need to qualify on both sides of the = sign?
    "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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Feb 2013
    Posts
    35
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    "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
    Thank you!

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,825
    Mentioned
    142 Post(s)
    Tagged
    0 Thread(s)
    @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/show...building-links

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

    Thanks
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  11. #11
    SitePoint Enthusiast
    Join Date
    Feb 2013
    Posts
    35
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    @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/show...building-links

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

    Thanks
    Thank YOU, sir!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •