SQL - Unknown column in where clause

Question

I am rather new with SQL and I can't for the life of me figure out why I can't do what I'm trying here. I'm trying get the least number of free seats in a package-trip, and group them by the package ID.

The first of the subqueries work, but the second is the one I can't figure out. It says

Unkown column SuperPaket in 'where clause'.

select Paket.PaketID as "SuperPaket", Beskrivning, Resa.AvgångStad, Resa.AvgångTid, Resa.AvgångDatum,

    (select AnkomstTid from Resa where Resa.ResID in 
        (select ResID from PaketResa where PaketResa.PaketID = SuperPaket and Ordningsnr =
            (select max(Ordningsnr) from PaketResa where PaketResa.PaketID = SuperPaket))) "AnkomstTid",

        (select min(LedigaPlatser) from
            (select sum(AntalPlatser - Count) "LedigaPlatser", ResID from(
                select Bokning.ResID, AntalPlatser, sum(Bokning.AntalBiljetter) as Count from 
                    (Resa inner join Bokning on Resa.ResID = Bokning.ResID) where Bokning.ResID in
                        (select PaketResa.ResID from PaketResa where PaketResa.PaketID = SuperPaket)
                group by Bokning.ResID order by Count desc)
            as CountTable group by ResID) 
        as T) "LedigaPlatser"

    from ((Paket inner join PaketResa on Paket.PaketID = PaketResa.PaketID) inner join Resa on PaketResa.ResID = Resa.ResID) group by Paket.PaketID;

Why does this work for the first subquery but not the second one?

Update.. The error seems to appear when I put a "sub-subquery" in a from clause. I don't know how to rewrite the problematic query to fix this problem though, and I would really appreciate some help. Thanks..

that looks very complicated, and the swedish column names don't really help ... — njzk2
Do you have a column called "SuperPaket" in the actual tables (not just as a label on the outermost select statement)? — Thilo
I suggest you to show table structure, sample data and desired result. Your query is way to complicated and bad formatted/written too, no table aliases and so on — Roman Pekar
Im using MySQL, and I have the following tables; pastebin.com/BkGUkrbJ That's the relevant ones. I don't have the column 'SuperPaket', that's an Alias for Paket.PaketID in the first select.. — Christopher Carlsson

Unknown Unknown · Accepted Answer · 2012-10-19T11:29:46

I don't think you can use column alias in a where clause in mysql and oracle

From mysql docs

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column:

I have no idea about sql server

Added : Your subquery probably not getting Paket.PaketId. Try modifying from clause of most outer query by adding

(select Paket.PaketID as "SuperPaket" from Paket)

as below

from (
      (Paket inner join PaketResa on Paket.PaketID = PaketResa.PaketID)   
inner join Resa on PaketResa.ResID = Resa.ResID),
      (select Paket.PaketID as "SuperPaket" from Paket)
group by Paket.PaketID;

Also remove alias from first select and just say select SuperPaket

SQL - Unknown column in where clause

2 Answers