[RESOLVED] comparing tuples

[kakki]

hello there,

i'm stuck at this last query:
i have two tables
table1:
- id1 pk
- id2 pk
....

table2:
- id1 fk
- id2 fk
- value

i need to find all entries in table 1, which have a value of "1" in table 2
sth. like this

select  * from table1 where (id1,id2) in ( select (id1,id2) from table2 where value='1' )

, but that didnt work...

how can i get these infos???

[Shrike]

No need to subquery, just use a normal join.

SELECT table1.id1, table1.id2
FROM table1
INNER JOIN table2
ON table1.id1 = table2.id1
AND table1.id2 = table2.id2
WHERE table2.value = 1

[kakki]

hmmm....

i didnt describe the problem properly
i have these tables:
table1:id1(pk),id2(pk)
table2:id1(fk),id2(fk),value

in table2 are SOME(but not all) entries of table1.
now i wanna check, whether there are entries left in table1, which are not in table2
my statement so far is:

select  * from table1 where (id1,id2) NOT IN ( select (id1,id2) from table2 where value='1' )

i checked the mysql-manual but their examples for subqueries didnt work...
and i already did a workaround with an outer join but that was not fast enough,
i just cant get this **** statement to work... can u help me???

[Shrike]

You can use a left join rather than straight join. I think a subquery is a little unnecessary.

[kakki]

thnx,
i'll try to live with that: "Keep It Simple, Stupid." .... :-)