SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Unknown column in 'on clause' in Multiple Joins

    Hello, Following is the query I'm using with multiple joins.

    Code:
    SELECT 
    `tickets`.`ID`, `tickets`.`ticketID`, `tickets`.`JobID`, `tickets`.`truck_number`, `tickets`.`material`, `tickets`.`asce_number`, `tickets`.`row_number`, `tickets`.`disposal_site` as site, `job_sites`.`name` as site, `tickets`.`disposal_date` as date, `tickets`.`work_number`, `tickets`.`type`, `jobs`.`ID`, `jobs`.`job_name`, `trucks`.`ID`, `trucks`.`contractor`, `contractors`.`company` as Contractor, `trucks`.`truck_capacity` as Capacity, `trucks`.`truck_license_plate`, `materials`.`ID`, `materials`.`type` AS material_type FROM (`tickets`) 
    
    JOIN `jobs` ON `jobs`.`ID` = `tickets`.`JobID` 
    JOIN `job_sites` ON `job_sites`.`ID` = `tickets`.`disposal_site` 
    JOIN `contractors` ON `contractors`.`ID` = `trucks`.`contractor` 
    JOIN `trucks` ON `trucks`.`ID` = `tickets`.`truck_number` 
    JOIN `materials` ON `materials`.`ID` = `tickets`.`material`
    problem is with "JOIN `contractors` ON `contractors`.`ID` = `trucks`.`contractor` "
    It says #1054 - Unknown column 'trucks.contractor' in 'on clause'

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    write your joins in a sensible sequence so that you join only to tables that have previously been mentioned
    Code:
      FROM tickets 
    INNER
      JOIN jobs 
        ON jobs.ID = tickets.JobID 
    INNER
      JOIN job_sites 
        ON job_sites.ID = tickets.disposal_site 
    INNER
      JOIN trucks 
        ON trucks.ID = tickets.truck_number 
    INNER
      JOIN contractors 
        ON contractors.ID = trucks.contractor 
    INNER
      JOIN materials 
        ON materials.ID = tickets.material
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot. It worked like a charm!!
    Quote Originally Posted by r937 View Post
    write your joins in a sensible sequence so that you join only to tables that have previously been mentioned
    Code:
      FROM tickets 
    INNER
      JOIN jobs 
        ON jobs.ID = tickets.JobID 
    INNER
      JOIN job_sites 
        ON job_sites.ID = tickets.disposal_site 
    INNER
      JOIN trucks 
        ON trucks.ID = tickets.truck_number 
    INNER
      JOIN contractors 
        ON contractors.ID = trucks.contractor 
    INNER
      JOIN materials 
        ON materials.ID = tickets.material


Tags for this Thread

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
  •