Hi,

I can't find a way to formulate my query.

Here is the rough picture:

I have 3 tables: A, B, C

A n----1 B

B 1----n C

Composite PKeys:

B: fields a;b A: fields a;b;c C: fields a;b;d

I need the entities from A whom parent B hasn't got any related C

Sounds like "exists" or "not in", but the corresponding predicate doesn't seem to account for multiple fields.

Maybe I should turn the query differently to only deal with single fields, but I can't figure out how to deal with that.

I first thought (a,b)\€ XY <=> (a\€X) v (b\€Y) but I haven't got a XY for my research set so that's just wrong.

Any idea on how to deal with that?

Jessynoo wrote:

Hi,

I can't find a way to formulate my query.

Here is the rough picture:

I have 3 tables: A, B, C

A n----1 B

B 1----n C

Composite PKeys:

B: fields a;b A: fields a;b;c C: fields a;b;d

I need the entities from A whom parent B hasn't got any related C

Sounds like "exists" or "not in", but the corresponding predicate doesn't seem to account for multiple fields.

Subqueries always work with 1 field, you can't do: select * from a where a.b and a.c not in (select b, c from bar)

(unfortunately).

Though, you can join so if you do:

SELECT  A.*
FROM    A INNER JOIN B
        ON A.a = B.a
        AND A.b = B.b
        LEFT JOIN C
        ON B.a = C.a
        AND B.b = C.b
WHERE   C.a IS NULL
        AND 
        C.B IS NULL

(I might have hte fk's wrong, but you get the idea). The idea is that for a B which doesn't have a related C, the C fields are NULL. So simply join and test for that

THe exist query will work, but you then have to make it a co-related subquery with aliassing, which is a bit more work.

Thanks Frans, I'll go with that