aspose file tools*
The moose likes JDBC and the fly likes sql syntax question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql syntax question" Watch "sql syntax question" New topic
Author

sql syntax question

Asher Tarnopolski
Ranch Hand

Joined: Jul 28, 2001
Posts: 260
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


Asher Tarnopolski
SCJP,SCWCD
Chris Mathews
Ranch Hand

Joined: Jul 18, 2001
Posts: 2712
You are close. It should be:

To reduce overhead you should replace the * with the complete list of columns for T1.
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1740
    
    2
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
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1740
    
    2
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
Ranch Hand

Joined: Jul 28, 2001
Posts: 260
thank's, i tried it yesterday too, it doesn't work either
any other ideas?
Leslie Chaim
Ranch Hand

Joined: May 22, 2002
Posts: 336
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?


Normal is in the eye of the beholder
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1740
    
    2
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
Ranch Hand

Joined: Jul 28, 2001
Posts: 260
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
Ranch Hand

Joined: Jul 28, 2001
Posts: 260
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 ]
Chris Mathews
Ranch Hand

Joined: Jul 18, 2001
Posts: 2712
There is your problem. MySQL does not support subqueries. Subqueries are supposedly a planned feature for MySQL 4.0.
Asher Tarnopolski
Ranch Hand

Joined: Jul 28, 2001
Posts: 260
thanX chris....
it always comes from somewhere you don't expect it to come from...
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1740
    
    2
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:

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

Joined: Jul 28, 2001
Posts: 260
michael-> it works! thank's a lot.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sql syntax question
 
Similar Threads
Is there a way to write finder method by join of two table in websphere 4.0
Getting one Row from JOIN query
SQL: howto retrieve only the first row?
Stucked in group by !!
how to get child table values from parent query