SQL - Unable to condition an alias due to Unknown column in where clause

Question

I got the following query, but when I execute it I get an error - #1054 - Unknown column 'adress' in 'where clause' I am wondering why? When I remove the where clause it normally appears as a column, no idea why it throws an error.

SELECT zlec_status.nazwa                       AS Status, 
       piorytet.nazwa                          AS Priorytet, 
       Concat(koord.imie, ' ', koord.nazwisko) AS `Koordynator`, 
       Concat(zlec_adresy.miasto, ' - ', zlec_adresy.ulica, ' ', 
       zlec_adresy.oddzial) 
                                               AS `adress`, 
       zlec_z_dnia,zlec_id,
       zlec_nr, 
       zlec_do, 
       zlec_ogran, 
       awizacje, 
       awizacja_na_dzien, 
       termin_zamkniecia, 
       tresc, 
       uwagi 
FROM   zlec 
       INNER JOIN koord 
               ON zlec.koord = koord.id 
       INNER JOIN zlec_adresy 
               ON zlec.zlec_addres = zlec_adresy.id 
       INNER JOIN piorytet 
               ON zlec.priorytet = piorytet.id 
       INNER JOIN zlec_status 
               ON zlec.status_zlecenia = zlec_status.id 
WHERE `adress` LIKE '%Some%'

possible duplicate of [Can you use an alias in the WHERE clause in mysql?](http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql) — Jakub Kania, Jul 13 '15 at 21:50

score 2 · Answer 1 · answered Jul 13 '15 at 21:48

2

You can't uses aliases in the where clause. Use the original concatenated columns

It is not allowable to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed.

where Concat(zlec_adresy.miasto, ' - ', zlec_adresy.ulica, ' ', 
   zlec_adresy.oddzial) LIKE '%Some%'

answered Jul 13 '15 at 21:48

juergen d

201,996
37
293
362

This solves my problem :) I guess that gordon was first anyway so Ill upvote you – Kavvson Empcraft Jul 13 '15 at 21:51

score 2 · Accepted Answer · answered Jul 13 '15 at 21:49

2

You cannot use a column alias defined in a select in a where clause for that select. You have two choices. MySQL extends the use of having, so you can use that:

HAVING `adress` LIKE '%Some%'

Or, you can use the full expression:

Concat(zlec_adresy.miasto, ' - ', zlec_adresy.ulica, ' ', 
       zlec_adresy.oddzial) LIKE '%Some%'

Or, you can use a subquery, but I wouldn't recommend that.

answered Jul 13 '15 at 21:49

Gordon Linoff

1,242,037
58
646
786

Probably a bit overkill in this case, but what do you think about building it up as an outer apply and referencing it that way? Cut once measure twice? EDIT: Not supported in MySQL for a start... – LordBaconPants Jul 13 '15 at 22:15

SQL - Unable to condition an alias due to Unknown column in where clause

2 Answers2