#1054 - Unknown column 'a.ArtikelID' in 'on clause'

Question

Anybody got an idea what is wrong with this one?

At first I had a query without subselect, but I have articles in a database and each article can have multiple prices and I just need the actual one in my total result-set.

The error is as said in title "#1054 - Unknown column 'a.ArtikelID' in 'on clause' " but the columns are all perfectly valid, before I split price calculation into a subselect all columns were perfectly fine.

SELECT `a`.`ArtikelNr`, `at`.`name`, `a`.`LagerPlatz`, `a`.`Bestand`, `a`.`Reserviert`, `a`.`Sperrlager`, (`a`.`Bestand` - `a`.`Reserviert` - `a`.`Sperrlager`) AS `GesamtBestand`, `m`.`Prozent` AS `MwSt%`, `preis`.`Netto` AS `Einzelpreis`, (`preis`.`Netto` * `a`.`Bestand`) AS `Gesamtpreis`
FROM `artikel` a,
    (
        SELECT `psub`.`Netto`
        FROM `artikel` asub
        LEFT JOIN `preis` psub ON `asub`.`ArtikelID` = `psub`.`ArtikelID`
        WHERE `asub`.`Bestandfuehren` = '1' AND `psub`.`PreisArtID` = 2 AND (`psub`.`Bis` = '0001-01-01 00:00:00' OR `psub`.`Bis` >= NOW())
        ORDER BY `psub`.`Von` DESC
        LIMIT 1
    ) AS preis
LEFT JOIN `artikel_text` at ON `a`.`ArtikelID` = `at`.`ArtikelID`
LEFT JOIN `system3zentral`.`mehrwertsteuer` m ON `a`.`MehrwertsteuerID` = `m`.`MehrwertsteuerID`
WHERE `a`.`Bestandfuehren` = '1' AND `at`.`SpracheID` = 1 AND `preis`.`Netto` IS NOT NULL
GROUP BY `a`.`ArtikelNr`
ORDER BY CASE WHEN LENGTH(`a`.`Lagerplatz`) <= 2 THEN 1 ELSE 0 END, `a`.`Lagerplatz` ASC

Answer 1

Solved it myself, thanks @Strawberry...

Here the solution for anybody who has the same problem when building a query...

SELECT `a`.`ArtikelNr`, `at`.`name`, `a`.`LagerPlatz`, `a`.`Bestand`, `a`.`Reserviert`, `a`.`Sperrlager`, (`a`.`Bestand` - `a`.`Reserviert` - `a`.`Sperrlager`) AS `GesamtBestand`, `m`.`Prozent` AS `MwSt%`, `preis`.`Netto` AS `Einzelpreis`, (`preis`.`Netto` * ABS(`a`.`Bestand`)) AS `Gesamtwert`
FROM `artikel` a
LEFT JOIN
    (
        SELECT `suba`.`ArtikelID` AS artID, `subp`.`Netto`
        FROM `artikel` suba
        LEFT JOIN `preis` subp ON `suba`.`ArtikelID` = `subp`.`ArtikelID`
        WHERE `suba`.`Bestandfuehren` = '1' AND `subp`.`PreisArtID` = 2 AND (`subp`.`Bis` = '0001-01-01 00:00:00' OR `subp`.`Bis` >= NOW())
        ORDER BY `subp`.`Von` DESC
    ) preis ON `a`.`ArtikelID` = `preis`.`artID`
LEFT JOIN `artikel_text` at USING(ArtikelID)
LEFT JOIN `system3zentral`.`mehrwertsteuer` m USING(`MehrwertsteuerID`)
WHERE `a`.`Bestandfuehren` = '1' AND `at`.`SpracheID` = 1 AND `preis`.`Netto` IS NOT NULL
GROUP BY `a`.`ArtikelNr`
ORDER BY CASE WHEN LENGTH(`a`.`Lagerplatz`) <= 2 THEN 1 ELSE 0 END, `a`.`Lagerplatz` ASC