List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 11 1999 1:16pm
Subject:Re: How to do a select within a select
View as plain text  
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

Thread
How to do a select within a selectShafir11 May
  • Re: How to do a select within a selectChristian Mack11 May