Hi Jerry,
I don't think that will work as I think
you'll only get hits in tablea on what exists in
tableb, i.e. you wont get 1/a as your JOIN will exclude
that as it will only join for non null existencies.
Your query will return 2/b and 3/c as they exist in tableb
and have flag != 'Y' and miss 1/
What about
SELECT tablea.* FROM tablea AS a JOIN tableb AS b ON a.a_id = b.a_id
WHERE b.b_id IS NULL
OR b.flag != "Y";
The WHERE clause should exclude existing records where the flag is Y,
include
existing records where the flag is not Y, and include records from tablea
that don't have matching r
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 affe
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 r
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
André Hänsel skrev:
Hello list,
I have two tables:
Table A
a_id name
1a
2b
3c
Table B
b_id a_id flag name
12yx
22ny
3
Hello list,
I have two tables:
Table A
a_id name
1a
2b
3c
Table B
b_id a_id flag name
12yx
22ny
33nz
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
