SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with LEFT JOIN Query

    Hello !

    Well, I have a query like this:

    Code:
    SELECT *,ba.areaName AS areaName2 from vendor v, subscriptions s, areas a
    LEFT JOIN areas AS ba ON v.areaID2 = ba.areaID
    WHERE s.subsID = v.subsID AND a.aid = v.areaID AND v.vendorDel = '0'
    But this shows below error:
    HTML Code:
    There is some error in select query in table :vendor v, subscriptions s, areas a Unknown column 'v.areaID2' in 'on clause'
    Please help me what is the FIX to this error, because, in table vendor, column areaID2 is present.

    Thanks a multi billion !

    Regards
    ZH

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Can you provide the table definitions for vendor, subscriptions and areas?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you are mixing the deprecated old style comma joins with the preferred explicit JOIN syntax

    use only explicit JOIN syntax

    so change this --
    Code:
    SELECT ... from vendor v, subscriptions s, areas a
    LEFT JOIN areas AS ba ON v.areaID2 = ba.areaID
    WHERE s.subsID = v.subsID AND a.aid = v.areaID AND v.vendorDel = '0'
    to this --
    Code:
    SELECT ...
      FROM vendor AS v
    INNER
      JOIN subscriptions AS s
        ON s.subsID = v.subsID
    INNER
      JOIN areas AS a
        ON a.aid = v.areaID
    LEFT OUTER
      JOIN areas AS ba 
        ON ba.areaID = v.areaID2
     WHERE v.vendorDel = 0
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    The DBMS doesn't know how to resolve this field because you're using it in a JOIN that doesn't involve the vendor table.
    Basically this is what the DBMS sees:

    Code SQL:
    SELECT
       *,
       ba.areaName AS areaName2
    FROM
       vendor v,
       subscriptions s,
       (areas a LEFT JOIN areas AS ba ON v.areaID2 = ba.areaID) -- <-- JOIN on a propery of 'v', but 'v' is not involved in this JOIN.
    WHERE
       s.subsID = v.subsID
       AND a.aid = v.areaID
       AND v.vendorDel = '0'

    What you'd need to do instead is move those conditions from the WHERE clause to JOIN criteria so the DBMS will know what you want.
    I don't have you database, so I can't test this, but this should work; or at least make clear to the DBMS what it is you want

    Code SQL:
    SELECT
       *,
       ba.areaName AS areaName2
    FROM
       vendor v
          INNER JOIN
             subscriptions s
          ON
             s.subsID = v.subsID AND v.vendorDel = '0'
          INNER JOIN
             areas a
          ON
             a.aid = v.areaID
          LEFT JOIN
             areas AS ba
          ON
             v.areaID2 = ba.areaID)

    Also, please refrain from using SELECT * -- just select the fields you actually want.

    Edit:

    @r937 ; beat me to it!
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi !

    Thanks a lot Masters ! You people are always great.

    @r937, this was my first question in mySQL forum, after a long time, and believe me, I thought already that you will give me the solution.

    @ScallioXTX, thanks a lot for the great description and in fact TEACHING me the cause of the issue.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
  •