This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes sql query 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 "sql query" Watch "sql query" New topic

sql query

marys joseph

Joined: May 14, 2004
Posts: 20

I need help for a sql query, i have 2 tables.

table a: studentid sname
table b: studentid, studentNumber.

sometimes same studentid has 1 or more studentnumbers.

I need to display studentid,studentnumber,sname

when I join I always get multiple rows of same student because the student has more than 1 student number. I want to get only one studentnumber if the student has more. how can i do that. I used distinct keyword but the rows are not actually different.

Jeff Pavlocak

Joined: Apr 26, 2004
Posts: 16
Try the following:

SELECT table_a.studentid, MAX(studentnumber), sname
FROM table_a, table_b
WHERE table_a.studentid = table_b.studentid
GROUP BY studentid, sname;
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Marys,

Did Jeff's suggestion work? I would expect it to produce the same results as DISTINCT.

When you want to remove duplicates from a result set where the rows are identical you tend to have to resort to database pseudo-columns such as Oracle's rowid.

If you're still having trouble you might want to check that out.

Jeanne Boyarsky
internet detective

Joined: May 26, 2003
Posts: 30130

I would expect Jeff's example to work and produce different results than distinct. Since the student number is different, the rows aren't exactly the same and distinct wouldn't be able to filter them out.

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
I agree. Here's the link:
subject: sql query
Similar Threads
describing the table!!!...
Magic Number 7 ??
Arrays and strings---a little help, please
Arrays and strings
Multiple Object Select Problems