SQL Alias / WHERE

Question

I come up with a dumb question, but i'm stuck since 20min and can't figure out why this isn't working..

SELECT `A`.*, `B`.`sei` AS `seiAlbum` FROM `Lea_Picture` AS `A` LEFT JOIN 
`Lea_Album` AS `B` ON `A`.`idAlbum` = `B`.`idAlbum` WHERE (`seiAlbum` LIKE 
'%album_1%') ORDER BY `seiAlbum` ASC LIMIT 50

I got a

#1054 - Unknown column 'seiAlbum' in 'where clause'

Related tables :

    CREATE TABLE IF NOT EXISTS `Lea_Album` (
      `idAlbum` int(11) NOT NULL AUTO_INCREMENT,
      `sei` varchar(63) COLLATE utf8_unicode_ci DEFAULT NULL,
      `name` varchar(63) COLLATE utf8_unicode_ci DEFAULT NULL,
      `type` varchar(63) COLLATE utf8_unicode_ci DEFAULT NULL,
      `width` int(11) DEFAULT NULL,
      `height` int(11) DEFAULT NULL,
      PRIMARY KEY (`idAlbum`)
    );

    CREATE TABLE IF NOT EXISTS `Lea_Picture` (
      `idPicture` int(11) NOT NULL AUTO_INCREMENT,
      `sei` varchar(63) COLLATE utf8_unicode_ci DEFAULT NULL,
      `name` varchar(63) COLLATE utf8_unicode_ci DEFAULT NULL,
      `title` varchar(127) COLLATE utf8_unicode_ci DEFAULT NULL,
      `order` int(11) DEFAULT NULL,
      `pictImage` varchar(127) COLLATE utf8_unicode_ci DEFAULT NULL,
      `idAlbum` int(11) DEFAULT NULL,
      PRIMARY KEY (`idPicture`)
    );

Any clue ?

Indeed ! For a lot of reasons, i can't simply use `B`.`sei` in my WHERE so i used a subquerie.Thanks you all for your help ;-) — rou6e, Mar 17 '15 at 11:12

score 1 · Answer 1 · answered Mar 17 '15 at 10:22

you cant give alias name in where clause you have to give orignal column name

give orignal column name b.sei in where clause

SELECT A.*, B.sei AS seiAlbum FROM Lea_Picture AS A LEFT JOIN 
Lea_Album AS B ON A.idAlbum = B.idAlbum WHERE (B.sei LIKE 
'%album_1%') ORDER BY B.sei ASC LIMIT 50

score 1 · Answer 2 · answered Mar 17 '15 at 10:22

Column alias is applied outside of the query. You can't refer to it in the same query.

So you either need to do

where `B`.`sei` LIKE '%album_1%'

(the same applies to the use in order by of course)

Or you need to wrap the whole query and apply the filter on the nested query:

select *
from
(
 SELECT `A`.*, `B`.`sei` AS `seiAlbum` FROM `Lea_Picture` AS `A` LEFT JOIN 
`Lea_Album` AS `B` ON `A`.`idAlbum` = `B`.`idAlbum`
) A
WHERE (`seiAlbum` LIKE '%album_1%') 
ORDER BY `seiAlbum` ASC LIMIT 50

seiAlbum isn't in-scope inside the nested query, just like B.sei isn't in-scope outside of it.

score 0 · Answer 3 · answered Mar 17 '15 at 10:24

0

This is due to the WHERE clause being executed before the SELECT clause.

Try using a sub-query such as;

SELECT * FROM (SELECT `A`.*, `B`.`sei` AS `seiAlbum` FROM `Lea_Picture` AS `A` LEFT JOIN 
`Lea_Album` AS `B` ON `A`.`idAlbum` = `B`.`idAlbum`) A
 WHERE (`seiAlbum` LIKE 
'%album_1%') ORDER BY `seiAlbum` ASC LIMIT 50

answered Mar 17 '15 at 10:24

JammoD

419
5
15

This has nothing to do with order of execution. It's only about scopes. Your query will be executed the same as if you just replaced `seiAlbum` with `B.sei` in the nested query. – Luaan Mar 17 '15 at 10:27
I had the understanding that when SQL executes a query it follows an order of execution and upon each stage of that execution it creates a virtual table which is then passed to the next stage. This is why the alias does not work with the WHERE clause but does work with the ORDER BY Clause because the SELECT statement has then been run and applied the alias name to the virtual table. [link]http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/[/link] – JammoD Mar 17 '15 at 12:11

SQL Alias / WHERE

3 Answers3