Posted by hayden on 11/28/05 21:36

Steve wrote:
> > Hello, I'm using PHP 4 and MySQL 4. Is it possible to write a query
> > that joins two tables, but if one value cannot be matched, the row
> > would not be excluded, but a value could be substituted. For example,
>
> > How could I write a single query tnat would give the following results?
>
> > USERNAME TOTAL_SALES
> > ---------------------------------------------------------------
> > myusername 125.00
> > (user no longer exists) 55.00
>
>
> SELECT
> CASE ISNULL(username)
> WHEN 1 THEN "(user no longer exists)"
> ELSE username
> END AS "username",
> total_sales
> FROM sales
> LEFT OUTER JOIN users ON sales.userid = users.userid

This query works fine as is, but I can't seem to use the generated
field in a WHERE clause at the end. Say you have a much larger table
like the one mentioned above, and you want to add a "WHERE username =
'(user no longer exists)' OR username = 'myusername'" to the end.
Based on what I see in my tests, you won't get the '(user no longer
exists)' records because the WHERE uses the original username, not the
one generated by the CASE/AS clause above.

Any help would be appreciated!

Thanks,
Bill

[Back to original message]