Hello group,

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é

André Hänsel wrote:
> Hello group,
>
> 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".

Try:
SELECT A.a_id, A.name
FROM Table_A A
LEFT JOIN Table_B B ON A.a_id = B.a_id AND B.flag = "y"
WHERE B.b_id IS NULL

On Mon, 18 Sep 2006 23:16:42 +0200, André Hänsel wrote:
> 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.

It is time for homework questions already?

--
_ o
|/)

Peter H. Coffin wrote:
> On Mon, 18 Sep 2006 23:16:42 +0200, André Hänsel wrote:
>> 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.
>
> It is time for homework questions already?

Hm? What do you want to say? That I haven't made my homework?

What did I overlook?