sql syntax question

[Asher Tarnopolski]

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

[Chris Mathews]

You are close. It should be:

SELECT * FROM T1 WHERE T1.ID NOT IN (SELECT ID FROM T2 WHERE T1.ID = T2.ID)

To reduce overhead you should replace the * with the complete list of columns for T1.

[Michael Matola]

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 )

[Michael Matola]

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 Tarnopolski]

thank's, i tried it yesterday too, it doesn't work either
any other ideas?

[Leslie Chaim]

Try this, tweak to your database, if not Oracle, to do a left outer join
select t1.*
from t1, t2
where t1.id = t2.id(+)
and t2.id is null
Does that do the trick?

[Michael Matola]

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?

[Asher Tarnopolski]

folks, thank you for replies!
i'll try to run your codes later today and i'll let you know if they work.
i use mysqlas a db.

[Asher Tarnopolski]

ohh, i don't belive this easy trick takes so long to be solved but ... it still doesn't work!
here is the code:

import java.sql.*;

public class FromTable {
  
  public static void main (String [] arg)
  {
  try
  {
  Class.forName("com.mysql.jdbc.Driver"); 
  Connection connection=DriverManager.getConnection("jdbc:mysql://localhost/test");
  DatabaseMetaData dbmd=connection.getMetaData();
  System.out.println("Product: "+dbmd.getDatabaseProductName());
  System.out.println("Version: "+dbmd.getDatabaseProductVersion());
  
  Statement statement = connection.createStatement();
  ResultSet result = statement.executeQuery("SELECT * FROM t1 WHERE t1.id NOT IN (SELECT id FROM t2 WHERE t1.id=t2.id)");
  ResultSetMetaData data = result.getMetaData();
  int cols=data.getColumnCount();
  for(int i=1;i<cols+1;i++)
  {
  System.out.print(data.getColumnName(i)+" ");
  }
  System.out.println("");
  while(result.next())
  {
  System.out.print(result.getString(1)+" ");
  System.out.print(result.getString(2)+" ");
  System.out.println("");
  }
 
connection.close();
  System.out.println("Closed.");
  }  
  catch(ClassNotFoundException cnfe){System.out.println("DRIVER class not found");}
  catch(SQLException sqle){System.out.println("SQL Exception: "+sqle.getMessage());}
  }
};

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 ]

[Chris Mathews]

There is your problem. MySQL does not support subqueries. Subqueries are supposedly a planned feature for MySQL 4.0.

[Asher Tarnopolski]

thanX chris....
it always comes from somewhere you don't expect it to come from...

[Michael Matola]

Asher, if you rewrite Leslie's query to use LEFT JOIN syntax instead of (+), you should still be able to do this without a subselect:

select t1.*
  from t1 left join t2
    on t2.id = t1.id
 where t2.id is null

I don't have MySQL installed at work to test this, but it works in McKoi and MS Access.

[Asher Tarnopolski]

michael-> it works! thank's a lot.