• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql query

 
marys joseph
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi,

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.

thanks,
 
Jeff Pavlocak
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Jules
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34202
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Julian,
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic