SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql inner join error

    Hi

    I am constructing an inner join with php - below is the code that my php code resolves to:

    SELECT * FROM `comp-profile-01-general` a INNER JOIN `comp-profile-02-social` b ON `a.co_num` = `b.co_num` INNER JOIN `comp-profile-03-specialties` c ON `a.co_num` = `c.co_num` WHERE `a.co_num` = 300023

    I am getting the error:
    #1054 - Unknown column 'a.co_num' in 'where clause'

    What have i done wrong?


    Thanks.

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT
        *
    FROM 
        comp-profile-01-general AS a
    INNER JOIN
        comp-profile-02-social AS b
            ON a.co_num = b.co_num
    INNER JOIN
        comp-profile-03-specialties AS c
            ON a.co_num = c.co_num
    WHERE a.co_num = 300023

    When using an alias for a table the syntax is

    table_name AS alias_name
    Also do you need all fields from all three tables? If not just list the required fields only in the SELECT clause
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by kath View Post
    What have i done wrong?
    you've put backticks around the tablename.columnname combo

    so `a.co_num` is wrong, but `a`.`co_num` is right

    a.co_num is also right, and it's "best practice" not to use backticks at all, based on naming your tables and columns so that there is no need to use backticks
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •