At 12:19 -0400 5/9/02, Luc Foisy wrote:
>hmm. ok i see what you are getting at
>but you do that every time you use a where clause, do you not?
No, not really
>
>SELECT value FROM table WHERE value = 5
In this case value is used in the WHERE clause to determine which records
to select. Once selected, value can be listed in the column output list.
>
>SELECT IF( value > 0, value2, value3 ) AS 'value4' FROM table WHERE value4 =
>5
In this case, the query is illegal. Aliases cannot be used in WHERE
clauses. (You didn't actually try this query, right?) This is the same
kind of problem that I *think* you're having with SQL variables: Trying
to select a value at the same time that you use it to determine which values
to select.
Maybe it would help to see this same problem in another context. This
is from the Cookbook; it illustrates the same phenomenon of trying
to use a value two ways at once:
MIN() and MAX() find the endpoints of a range of values, but sometimes
when find a minimum or maximum value, you're also interested in other
values from the row in which the value occurs. For example, you can find
the largest state population like this:
mysql> SELECT MAX(pop) FROM states;
+----------+
| MAX(pop) |
+----------+
| 29760021 |
+----------+
But that doesn't show you which state has this population.
The obvious way to try to get that information is like this:
mysql> SELECT name, MAX(pop) FROM states WHERE pop = MAX(pop);
ERROR 1111 at line 1: Invalid use of group function
Probably everyone attempts something like that sooner or later, but it
doesn't work, because aggregate functions like MIN() and MAX() cannot be
used in WHERE clauses. The intent of the statement is to determine which
record has the maximum population value, then display the associated
state name. The problem is that while you and I know perfectly well what
we'd mean by writing such a thing, it makes no sense at all to MySQL. The
query fails because MySQL uses the WHERE clause to determine which records
to select, but it knows the value of an aggregate function only after
selecting the records from which the function's value is determined! So,
in a sense, the statement is self-contradictory.
>
>In the conditional join, the variable select does not require any
>information from the joined table, so I wouldn't think it would matter
>[the above is just because I like to argue and i think too much :)]
>
>The single select statement issues no error, and it does retrieve a value
>(just a little too late), it must be how the select statement is followed
>through (as in order of operations) Are all joins completed first? (hmm,
>that would make sense wouldn't it)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
