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]
