2

Suppose I've Below tables

student :

id  Name
-------------------
1   john
2   carlos
3   zoya
4   arab
5   amir

and,

email :

id      email   student_id
--------------------------
1   [email protected]      1
2   [email protected]      2
3   [email protected]      2
4   [email protected]      3
5   [email protected]      4

and Using sql query to get student name along with emails,

SELECT student.name, email.eid FROM student 
INNER JOIN email 
ON student.id = email.student_id

and it will give below output,

Name        eid
-------------------
john    [email protected]
carlos  [email protected]
carlos  [email protected]
zoya    [email protected]
amir    [email protected]

here the duplicate entry(not actually duplicate) for carlos returned, but i want only single student list for example like this, ( either [email protected] or [email protected] doesn't matter).

Name        eid
-------------------
john    [email protected]
carlos  [email protected]
zoya    [email protected]
amir    [email protected]

I've not much experience in sql,

I've used DISTINCT but it wont works .. please help

Not : I'm storing emails in different tables in case if there is need in future.

Sohan Patel
  • 183
  • 1
  • 1
  • 14
  • You can use `GROUP BY name` – abaracedo Aug 25 '14 at 17:59
  • Which one? Why b and not c – Strawberry Aug 25 '14 at 17:59
  • It's not a duplicate entry, the user carlos has two different emails. You can GROUP BY if you dont want two carlos users. – ChrisG Aug 25 '14 at 17:59
  • If you want any email address for a student not specific to old/recent criteria just add group by student id – M Khalid Junaid Aug 25 '14 at 17:59
  • @Strawberry it can be any one `b` or `c` doesn't matters – Sohan Patel Aug 25 '14 at 18:02
  • If it doesn't matter, then that implies you have bigger problems with your data! It *should* matter! – Strawberry Aug 25 '14 at 18:03
  • @Strawberry No !!! It really doen't matter .. only one email id is more than enough .. there is no need other .. – Sohan Patel Aug 25 '14 at 18:04
  • @Strawberry i guess user is inserting email on update criteria too :) because if there is no need for more than one email then user should implement the update logic to update previous email instead to insert new one – M Khalid Junaid Aug 25 '14 at 18:08
  • 1
    Thanks commentators for suggesting `GROUP By` especially @MKhalidJunaid .. results are exactly as expected. – Sohan Patel Aug 25 '14 at 18:11
  • See my Updated answer ... why i'm getting sql error on query line 4 .. ?? – Sohan Patel Aug 25 '14 at 19:14
  • 1
    @user3710243 - If you have a different error, you should ask a new question rather than hack up your old question, which makes the answers already given useless. – JohnP Aug 25 '14 at 19:16
  • Be VERY careful with MYSQL's group by http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html The results you're getting are how mysql works out of the box. But this is not how other RDBMS support group by statements. – xQbert Aug 25 '14 at 19:26
  • Question moved as new question here http://stackoverflow.com/questions/25493080/mysql-getting-error-with-inner-join-with-group-by-and-where-clause – Sohan Patel Aug 25 '14 at 19:38

2 Answers2

0

You have a problem, because some students have multiple emails and you are only looking for one. If you don't care which one, then use MIN() or MAX():

SELECT s.name, MIN(e.eid)
FROM student s INNER JOIN
     email e
     ON s.id = e.student_id
GROUP BY s.name;

If you do care which one, then you need to explain the logic for choosing amongst multiples.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Missing the `group by` ? In MySQL I guess you can even omit the `min()` as it will grab a random value by default – Andomar Aug 25 '14 at 18:10
-2

This should work

SELECT distinct student.name, email.eid FROM student 
INNER JOIN email 
ON student.id = email.student_id

Why you're storing emails in different table and then joining them, you can store them in students table itself, that makes design simpler and querying easier.

Registered User
  • 1,554
  • 3
  • 22
  • 37