wood burning stoves 2.0*
The moose likes JDBC and the fly likes Using Distinct Keyword in Select statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using Distinct Keyword in Select statement" Watch "Using Distinct Keyword in Select statement" New topic
Author

Using Distinct Keyword in Select statement

Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
Hi,
I'm making a join involving 5 tables, all with primary and foreign keys. For example, if one table has a unique empno(for employee table),another table(vacation) has many values for that same empno depending on the number of vacations that empno has taken.The unique primary key for vacation is say vacationno. Now, when I make the join (which involves getting personal information etc), based on the empno, I get multiple rows with the same data in it for an empno with mutliple entries in vacation table. If I try to use distinct in my select statement,
I get an error saying :
ORA-00920: invalid relational operator
My question is, when I use distinct, does it hold good for only the first column immediately after it, or to all columns in the select statement ? And why am I getting the above error ? When I type the select statement without the distinct clause, it runs properly. With distinct, i get the error.
Is there a way to get the desired result, ie, only one row in vacation table for multiple empno values ? I want to use empno as my selective criteria.
Any suggestions will be very heplful.
Thanks.
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
Could we see the statement? A group by might work.


Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
Raja Shekhar
Ranch Hand

Joined: Apr 02, 2001
Posts: 49
Hi Mallika...
U got The Master Table...and Detail Table....
to get The Personal Info U r making..JOIN...rite..
just try..
Select e.empno,e.name
from emp e,vac v
where e.empno=v.empno
and let me know...
chio
Raj
Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
Hi,
Thanks a lot Thomas and Raja. Wierd, but when I tried the query in sql+, it did not work. But when I tried it out with my java class, it worked !!!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using Distinct Keyword in Select statement
 
Similar Threads
when to index column(s)
count(*) vs count(id)
Choice of Implementation
A few doubts
Complicated SQL query help