MySQL Outer Join

Anon

Since there is no outer join (as far as I have found) in MySQL, is there any other way to get a query using a Group By to show nulls?  I looked for a function like Oracle's NVL, but couldn't find one for MySQL.  Please help!

Here is the query:
SELECT u.username, date_reg, level AS rank, COUNT(p.username) AS posts
FROM mbs_users u, mbs_posts p
WHERE u.username = p.username
ORDER BY posts DESC;

It stips out all the nulls in the mbs_posts table. Any help would be greatly appreciated.

Anon

I forgot to put the Group By in there, the query should look like this:

SELECT u.username, date_reg, level AS rank, COUNT(p.username) AS posts
FROM mbs_users u, mbs_posts p
WHERE u.username = p.username
GROUP BY p.username;

Anon

A LEFT JOIN ought to do the trick. Look it up in the PHP manual. http://www.php.net

Good luck!

rrowv

Yep that works. Since I couldn't find the words "outer join" anywhere in the mysql documentation, I thought it didn't have an outer join function. Guess I was wrong. I guess left join and right join never clicked as being an outter join. Thanks a bunch!

Anon

MySQL have outer join.

http://www.mysql.com/doc/J/O/JOIN.html

regards

Anon

Or you can simulate join...

SELECT table1.someField, table2.someField
FROM table1, table2
WHERE table1.someField = table2.someField
AND table1.someField = something

Anon

This won't include fields with NULL values, though, which is what he was looking for. LEFT JOIN does this.

Anon

SELECT table1.someField, table2.someField
FROM table1, table2
WHERE table1.someField = table2.someField
AND table1.someField IS NULL

that will include only NULL values

simulation is possible, join is shortcut

but, I post down there message that MySQL have inner and outer join

again...
http://www.mysql.com/doc/J/O/JOIN.html

Anon

This still doesn't produce the desired results.

WHERE table1.someField = table2.someField

This is the problem child. If either one of these is NULL, then the result of the comparison is also NULL because NULL is an indeterminate value. What is the result of the comparison "1=NULL"? You can't tell because NULL is indeterminate and you don't know what it really represents.

If both of them are NULL, the result is still NULL because you can't determine whether two unknown values are equal.

To properly display 1 row of output for every row in another table, regardless of whether the foreign key is NULL, you must (in the case of MySQL) use a LEFT JOIN or one of its synonyms.

There may be another way to do this, but an outer join is the only way that I'm aware of.

Anon

Best regards