Need help with LEFT JOIN Query

[zeeshanhashmi]

Hello !

Well, I have a query like this:

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:

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

[rcashell]

Can you provide the table definitions for vendor, subscriptions and areas?

[r937]

you are mixing the deprecated old style comma joins with the preferred explicit JOIN syntax

use only explicit JOIN syntax

so change this --

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

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

[ScallioXTX]

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:

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

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!

[zeeshanhashmi]

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.

[r937]