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/a.
Even if you change to a LEFT JOIN it won't work as you'll
get 2/b then as well as it has an occurence with flag = 'n'.
The way to do it is, I think (repeated from my earlier mail
in this thread):
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
Jerry Schwartz skrev:
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 records in tableb.
Unless I'm mistaken....
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
