Hi Mike,
yes it works:
mysql> CREATE TABLE tablea (a_id int unsigned);
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE tableb (b_id int unsigned, a_id int unsigned, flag
char(1));
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO tablea values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO tableb VALUES (1,2,'y'),(2,2,'n'),(3,3,'n');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT a.*
-> FROM tablea a
-> LEFT JOIN tableb b ON b.a_id = a.a_id AND b.flag = 'y'
-> WHERE b.b_id IS NULL;
+------+
| a_id |
+------+
| 1 |
| 3 |
+------+
2 rows in set (0.02 sec)
If you look at the resultset before the where
clause is applied it'll look like this (I've added b.b_id as well )
a.a_id b.b_id
1 NULL
2 1
3 NULL
you then apply the where and end up with 1 and 3.
As long as you every restriction in the JOIN clause
and only the IS NULL in the where clause it should work.
/Johan
mos skrev:
At 03:16 AM 9/20/2006, Johan Höök wrote:
Hi André,
you can do it like:
SELECT a.*
FROM tablea a
LEFT JOIN tableb b ON b.a_id = a.a_id AND b.flag = 'y'
WHERE b.b_id IS NULL;
/Johan
Johan,
I don't think that is going to work. How is it going to have
b.flag='Y' when it can't find the b record, because b.b_id is null?
Everything from b will be Null because that's what's what you're looking
for in the Where clause.
Mike
André Hänsel skrev:
Hello list,
I have two tables:
Table A
a_id name
1 a
2 b
3 c
Table B
b_id a_id flag name
1 2 y x
2 2 n y
3 3 n z
How can I find the rows from table A where there is no matching row
(joined
using a_id as key) in table B where flag is "y"?
So in this example I want the entries 1/a and 3/c from table A. 2/b
should
not be selected because there is a row in table B with a_id = 2 and
flag="y".
Understandable?
It seems quite impossible to me, but I cannot figure out a reason why
it is
impossible, either.
Regards,
André
--
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]
