> 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
> 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.
> ...Shafir Ahmad <shafir@stripped>
What you need here is a LEFT JOIN.
If 'user' is the table with the additional rows:
LEFT JOIN usercreate
ON user.userno = usercreate.userno
usercreate.userno IS NULL
You have to get a good SQL book.
There are multiple hints in the mailarchives.