Need help with LEFT JOIN Query

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

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

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

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! :slight_smile:

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.

:smiley: