Hi, You are absolutely right as per the test. I am confused. Infact, I had run a limit on the query. So, I got the result in the first two records. So, i was able to get a single ID which I was looking at.
My original requirement was a bit more complicated than the one I posted. I had to check 1 million records (a table) and half a million (b table). And, due to transactions the "a table" gets updated frequently resulting in different number of records. - Harish -----Original Message----- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Monday, January 31, 2005 6:48 PM To: mysql Cc: Harish; Michael Stassen Subject: Re: Need a query to get the difference of two tables Harish wrote: > Hi All, > > This query worked for me. > > SELECT a.address > FROM a > LEFT JOIN b ON a.id != b.iid AND b.message='y' Strange... that query should give far too many and wrong rows as a result, and it would take a long time to run on a big dataset... you are joining each row in table a with every row in table b with message='y', except that one potential row where id=iid... I did this test: mysql> use test; Database changed mysql> create table a(id int not null primary key,address varchar(80)); Query OK, 0 rows affected (0.02 sec) mysql> create table b(iid int,message enum('y','n') not null); Query OK, 0 rows affected (0.00 sec) mysql> insert into a values (1,'addr 1'),(2,'addr 2'), (3,'addr 3'),(4,'addr 4'),(5,'addr 5'); Query OK, 5 rows affected (0.08 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into b values (1,'y'),(3,'y'); Query OK, 2 rows affected (0.13 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select a.address from a left join b on a.id!=b.iid and b.message='y'; +---------+ | address | +---------+ | addr 1 | | addr 2 | | addr 2 | | addr 3 | | addr 4 | | addr 4 | | addr 5 | | addr 5 | +---------+ 8 rows in set (0.00 sec) How does my data differ from yours, as you got the result you wanted from this query? From your original post, I got the impression that this was what you wanted: mysql> select a.address -> from a -> left join b on b.iid=a.id and message="y" -> where b.iid is null; +---------+ | address | +---------+ | addr 2 | | addr 4 | | addr 5 | +---------+ 3 rows in set (0.02 sec) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
