Shafir wrote:
>
> Hi there,
>
> I'm a novice at this. I'm trying the following:
>
> I have two tables, user and usercreate, and they have
> a common field userno. Somewhere alog the way, one of the
> tables did not get updated, so I have more records in one
> of the tables. So I want to find the records that exist in
> one of the table but not in the other. How can I do that.
>
> I tried:
>
> SELECT userno FROM user WHERE userno NOT IN
> SELECT userno FROM usercreate;
>
> This seems to be an illegal operation, so i tried
>
> SELECT user.userno FROM user,usercreate WHERE
> user.userno!=usercreate.userno;
>
> annd this returns thousands of records when I only
> have hundreds!
>
> What kind of command would return what I am looking for?
>
> BTW, anyone can recommend a good book (not necessarily
> specific to mySQL) which talks about good DB design and
> optimization for speed/concurrent access by many users?
> Also a book/website to learn about the various stuff
> like what is a LEFT JOIN, RIGHT JOIN etc etc.
>
> Thanks!
>
> ...Shafir Ahmad <shafir@stripped>
Hi Shafir
What you need here is a LEFT JOIN.
If 'user' is the table with the additional rows:
SELECT
user.userno
FROM
user
LEFT JOIN usercreate
ON user.userno = usercreate.userno
WHERE
usercreate.userno IS NULL
You have to get a good SQL book.
There are multiple hints in the mailarchives.
Tschau
Christian
