'select as' and 'WHERE'

[KryptoS]

Hello,

I run into a little problem. Is this possible 'cause it's not working for me:

select field1 AS test FROM tbl_test WHERE test > 5;

I'm always getting the message:

Unknown column 'test' in 'where clause'

Is there a way to make this working?

visit my website at

http://www.phpscriptor.com

 

[ethorn10]

Pretty sure you can't use the alias in your where clause. Try:

select field1 AS test FROM tbl_test WHERE field1 > 5;

 

[AdaHacker]

Like ethorn10 said, you can't use a column alias in the WHERE clause. The reason for this is the order of execution in SQL. Bascially, the SELECT clause is executed after the FROM and WHERE, so the alias hasn't actually been set yet when the WHERE clause is executing. Joe Celko provides an interesting explanation of this in SQL for Smarties.

 

[KryptoS]

select field1 AS test FROM tbl_test WHERE field1 > 5;

Yes I know that, but this is just a simple example. In reality the select is a little more complex.

I assume they had a good reason to do that. But it would 've made things a lot easier if you could use the as-field in the where or orde or ...


visit my website at

http://www.phpscriptor.com

 

[ethorn10]

Fire off the real example, if you can and I'm sure somebody can help make it work.

 

[r937]

In reality the select is a little more complex.

in that case, do this --

SELECT test
  FROM ( SELECT field1 AS test 
           FROM tbl_test ) AS d
 WHERE test > 5;

r937.com | rudy.ca

 

[KryptoS]

This is a little of the sql-string. I left out some more joins 'cause they are not making the problem.

As you see, I have a table tbl_company with 2 foreign keys to the table tbl_contacten. I want to use a where on the field Adres, Contact1 and Contact2. If I could use the AS-field I could do e.g. Adres LIKE '%street%', now I have to do (Street LIKE '%street%' OR Number LIKE '%street%').

SELECT 
  CONCAT_WS(" ",tbl_company.Street,tbl_company.Number) AS `Adres`,
  CONCAT_WS(" ",tbl_contact1.Firstname,tbl_contact1.Middlename,tbl_contact1.Lastname) AS `Contact1`,
  CONCAT_WS(" ",tbl_contact2.Firstname,tbl_contact2.Middlename,tbl_contact2.Lastname) AS `Contact2` FROM `tbl_company`
 LEFT JOIN `tbl_contacten` AS `tbl_contact1` ON tbl_contact1.ID=tbl_company.FK_contact1
 LEFT JOIN `tbl_contacten` AS `tbl_contact2` ON tbl_contact2.ID=tbl_company.FK_contact2

visit my website at

http://www.phpscriptor.com

 

[DonQuichote]

That is why the WHERE clause is not possible (it is meant to PRE-select the records) and why the HAVING clause exists (which does a post-selection). So basically do something like

SELECT ... WHERE ... HAVING Address LIKE '%Whatever%';

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

using HAVING without a GROUP BY clause is a hack, and will cause you more problems than it solves

just do this --

SELECT Adres
     , Contact1
     , Contact2
  FROM (
       SELECT CONCAT_WS(' '
                       ,tbl_company.Street
                       ,tbl_company.Number) AS Adres
            , CONCAT_WS(' '
                       ,tbl_contact1.Firstname
                       ,tbl_contact1.Middlename
                       ,tbl_contact1.Lastname) AS Contact1
            , CONCAT_WS(' '
                       ,tbl_contact2.Firstname
                       ,tbl_contact2.Middlename
                       ,tbl_contact2.Lastname) AS Contact2 
         FROM tbl_company
        LEFT OUTER
         JOIN tbl_contacten AS tbl_contact1 
           ON tbl_contact1.ID = tbl_company.FK_contact1
        LEFT OUTER
         JOIN tbl_contacten AS tbl_contact2 
           ON tbl_contact2.ID = tbl_company.FK_contact2
       ) AS d
 WHERE Adres LIKE '%street%'

r937.com | rudy.ca

 

[KryptoS]

Sorry, if I'm going to far with my questions but...

using HAVING without a GROUP BY clause is a hack, and will cause you more problems than it solves

Can you tell me where it can go wrong? Maybe an example? 'Cause seems to me that it's working with HAVING.

By the way, what's the difference between HAVING and WHERE? Are there rules when/why you should use having, and when/why WHERE?

visit my website at

http://www.phpscriptor.com

 

[r937]

WHERE filters detail rows produced by the FROM clause

HAVING filters group rows produced by the GROUP BY clause



r937.com | rudy.ca

 

[DonQuichote]

This is not as I have learned it. I do not see why HAVING should need a GROUP BY. The way I learned it is:

WHERE uses indexes on tables (if they exist). So WHERE likes to search directly on fields rather than expressions. The WHERE clause is used to limit the number of records selected before the contents of the rows themselves are read.

HAVING works after the selection to filter out rows on expressions rather than direct fields. So HAVING always works on all rows that are produced by the WHERE clause.

So If I have to select all points within a circle, I put "square" coordinate limits in the WHERE clause and the full quadratic formula in the HAVING clause.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

HAVING works after the selection to filter out rows on expressions rather than direct fields. So HAVING always works on all rows that are produced by the WHERE clause.

i am sorry to have to tell you, but this is wrong




HAVING works on group rows, which are rows produced by the aggregation or collapsing of multiple rows into one row per GROUP BY column(s)

if there is no GROUP BY clause, HAVING may still be used, but this is an outlier situation, and will clearly only work when the SELECT clause contains only non-aggregates expressions, or only aggregates

HAVING without GROUP BY is a hack, in my oprinion, and you should find a different way to accomplish whatever it's doing

as i said, HAVING in almost all cases is used only with GROUP BY



r937.com | rudy.ca

 

[DonQuichote]

You are right. I checked the MySQL documentation:

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and allows HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

The same page (

http://dev.mysql.com/doc/refman/5.1/en/select.html)

also says:

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.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)