[thelist] SQL command help
CDitty
mail at redhotsweeps.com
Sun Nov 12 17:18:14 CST 2000
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 !
More information about the thelist
mailing list