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.

    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;

      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!

        Or you can simulate join...

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

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

            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

              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.

                Write a Reply...