MySQL -- query that displays rows that can"t be joined

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,
if there were two tables

SALES
----------
USER_ID INTEGER UNSIGNED,
TOTAL_SALES FLOAT

and

USERS
-----------
USER_ID INTEGER UNSIGNED,
USERNAME VARCHAR(32) NOT NULL

and they contained the following data

SALES.USER_ID SALES.TOTAL_SALES
------------------------------------------------------------ -----
4 55.00
2 125.50

USERS.USER_ID USERS.USERNAME
------------------------------------------------------------ --
2 myusername

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

Thanks for your help, - Dave

Re: MySQL -- query that displays rows that can"t be joined

> 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

---
Steve

Re: MySQL -- query that displays rows that can"t be joined

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

Re: MySQL -- query that displays rows that can"t be joined

Have you tried a HAVING clause and the end of you sql?

e.g. HAVING username = 'myusername'

Re: MySQL -- query that displays rows that can"t be joined

I'd never used HAVING before. That did the trick. Thanks!

Re: MySQL -- query that displays rows that can"t be joined

> 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.

Refer to the original column name, not the aliased name. If it helps,
make the aliased name obviously different from the original...

SELECT
CASE ISNULL(username)
WHEN 1 THEN "(user no longer exists)"
ELSE username
END AS "username_x",
total_sales
FROM sales
LEFT OUTER JOIN users ON sales.userid = users.userid
WHERE ISNULL(username) OR username = "myusername"

---
Steve

Re: MySQL -- query that displays rows that can"t be joined

> Have you tried a HAVING clause and the end of you sql?
>
> e.g. HAVING username = 'myusername'


That's interresting. I was sure that HAVING clause can
only be used in GROUP BY selects. It's probably some
MySQL feature. It does not work in Oracle 8i (I mean
I can't use aliased name in HAVING clause in it).

Hilarion