hi, i have 2 tables (t1 and t2) . id is a primary key in t1 and it's referenced by t2. now, i wanna get all rows from t1 which are NOT found in t2. i wrote this: SELECT * FROM T1 WHERE T1.ID<>(SELECT ID FROM T2 WHERE T1.ID=T2.ID) i get an error saying that there is a syntax error between the ()... what's the problem / is there any other better and WORKING!! way to do this? thanX
Since the subquery can return multiple rows, you need to use NOT IN in the main query, instead of <> select * from t1 where t1.id not in ( select t2.id from t2 where t2.id = t1.id ) Another common way of doing this is select * from t1 where not exists ( select 'x' from t2 where t2.id = t1.id )
Third way -- may or may not be relevant. If the list of fields of interests in t1 and t2 is the same (or at least union-compatible) and if your database supports MINUS, then something like the following is possible. select t1.id from t1 minus select t2.id from t2 But again, I'm just tossing the MINUS bit out there. It may not be right for your situation. Go with one of the other two versions.
Asher, I tried Chris's, Leslie's, and my queries against tables I created in Oracle and they all are working for me. Is there any more info you can provide? What database product are you using? What is the specific error message? Any more info on the tables?
ohh, i don't belive this easy trick takes so long to be solved but ... it still doesn't work! here is the code:
here is a result: Product: MySQL Version: 3.23.52-nt SQL Exception: Syntax error or access violation: You have an error in your SQL syntax near 'SELECT id FROM t2 WHERE t1.id=t2.id)' at line 1 [ October 20, 2002: Message edited by: Asher Tarno ]