[thelist] SQL command help

Warden, Matt mwarden at odyssey-design.com
Sun Nov 12 14:13:21 CST 2000


Chris, I always screw up the syntax for outer joins, but I'll give it a
shot...

> Here is the sql for what I know how to do.
> select bonus_plays.user_id, lottouser.id
> from bonus_plays, lottouser
> where bonus_plays.user_id = lottouser.id
>
> All I need to do is find the 1 row that is missing from the bonus_plays
> table.  Can anyone help?

select bonus_plays.user_id
from lottouser right join bonus_plays on bonus_plays.user_id = lottouser.id
where lottouser.id is null

Also, note that I got rid of the second column you were returning since, by
declaration, bonus_plays.user_ID has to be the same as lottouser.id (except
where they don't match, which is why i chose to return bonus_plays.user_id).

In the even that I did get the syntax wrong, I was attempting to include all
rows from bonus_plays matched up to all the rows in lottouser. Where there is
not a matching row in lottouser, the returned column(s) will be NULL. So, to
find the unmatched column, you must put in a where clause that searches for a
null column in the matched table.

Hopefully the real DB gurus will only be one or two into their Sunday brews if
what I suggested fails.

hth a bit,

--
mattwarden
mattwarden.com





More information about the thelist mailing list