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