• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

sql syntax question

 
Ranch Hand
Posts: 260
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 2713
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You are close. It should be:

To reduce overhead you should replace the * with the complete list of columns for T1.
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 260
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thank's, i tried it yesterday too, it doesn't work either
any other ideas?
 
Ranch Hand
Posts: 336
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 260
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 260
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2713
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There is your problem. MySQL does not support subqueries. Subqueries are supposedly a planned feature for MySQL 4.0.
 
Asher Tarnopolski
Ranch Hand
Posts: 260
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanX chris....
it always comes from somewhere you don't expect it to come from...
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 260
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
michael-> it works! thank's a lot.
reply
    Bookmark Topic Watch Topic
  • New Topic