[thelist] SQL command help

[CDitty]

Thanks Rudy.  Guess you might have had one to many beers.  :^)

Got the following error.
select id
from lottouser
where
NOT EXISTS
(select user_id from bonus_plays
where user_id = lottouser.id)

MySQL said: You have an error in your SQL syntax near 'EXISTS (select 
user_id from bonus_plays where user_id = l' at line 4

Any tips?

Thanks Chris


At 03:21 PM 11/12/00, you wrote:
> > Hopefully the real DB gurus will only be one or two into
> > their Sunday brews if what I suggested fails.
>
>
>i usualyy stop countng after three (hic) when the nfl is on
>
>;o)
>
>chris, matt's way does work -- use an outer join and check for null
>
>here's your original join query, using A and B as table names so that they
>stand out a bit more --
>
>    select A.user_id, B.id
>       from A, B
>       where A.user_id = B.id
>
>your question was to find the row that's missing from A
>
>matt's suggestion was
>
>   select A.user_id
>       from B right join A
>         on A.user_id = B.id
>     where B.id is null
>
>matt is definitely on the right track, he just got the tables backwards
>;o)
>
>if you want to find the row that's missing from A, you need to return the
>key from B that doesn't match any row from A, in other words you have to
>return the B.id field
>
>as to whether you should say "A left join B" or "B right join A" this
>depends on which table you want "all the rows" from
>
>by definition, an outer join gets all the rows from one table, plus any
>matching rows from the other table, with nulls in the columns where no
>matching row was found
>
>therefore if you are looking for rows of B that don't have matching rows of
>A -- which was your original question, chris -- then you need "B left join
>A"
>
>notice that it is the left or right "direction" which "points at" the table
>that you want "all the rows" from
>
>so you can also say "A right join B" which is the same as "B left join
>A" -- get it?
>
>
>anyhow, there is an even better way   ;o)
>
>chris wanted *only the missing* rows, so a NOT EXISTS query is perfect
>
>   select id
>      from B
>     where
>        NOT EXISTS
>          (select 1 from A
>             where userid = B.id)
>
>the nice part about the NOT EXISTS syntax is that it is easier to
>understand -- what is *not * required is isolated -- whereas the full outer
>join with null check is more, um, subtle as to what you are really after
>
>also, the database optimizer can do stuff like look in an index and see a
>record id and therefore *not retrieve* the actual row -- so NOT EXISTS
>syntax executes better
>
>
>
>the west coast games should be well into the first quarter, so we now
>return you to your regularly scheduled beer...
>
>
>rudy
>
>
>---------------------------------------
>For unsubscribe and other options, including
>the Tip Harvester and archive of TheList go to:
>http://lists.evolt.org Workers of the Web, evolt !