How to prevent duplicate some attributes when query

Question

I have two table. For example : Teacher(name, classCode) and ClassSession(classCode, session, name).

and the query is : find all teacher that teaches subject A.

For example:

Teacher
peter 1
Mary 2

ClassSession
1 summer database
1 spring database
...

And I query like this :

select * from teacher,ClassSession
where Teacher.classCode = ClassSession.classCode

And the result will be:

Peter 1 summer database
Peter 1 spring database

the result is duplicate attribute teacher name, because one subject can teach in multisessions. So, my question is : How can I query to avoid this duplicate. the result should only be Peter name.

Peter 1

you only want the name `peter` to come back? – Woot4Moo May 29 '13 at 12:28 — Woot4Moo, May 29 '13 at 12:28
yes. or moreover, just all attributes in table Teacher – hqt May 29 '13 at 12:30 — hqt, May 29 '13 at 12:30
Did you test DISTINCT keyword for your query ? – Mojtaba May 29 '13 at 12:34 — Mojtaba, May 29 '13 at 12:34

score 1 · Accepted Answer · answered May 29 '13 at 12:33

Try this:

select distinct tch.*
from teacher tch ,ClassSession cls
where Tch.classCode = Cls.classCode

However, I would recommend the following:

Use proper join syntax..

select distinct tch.*
from teacher tch 
join ClassSession cls on Tch.classCode = Cls.classCode

Also, not sure why you ever need to join two tables, since you are not pulling any information from the ClassCode table. If you need the second table for the class name, do the following:

select distinct tch.*,cls.Name
from teacher tch 
join ClassSession cls on Tch.classCode = Cls.classCode

Implicit syntax should not be used. – Kermit May 29 '13 at 12:34 — Kermit, May 29 '13 at 12:34

score 1 · Answer 2 · answered May 29 '13 at 12:33

In my understanding this:

find all teacher that teaches subject A

the query should be this:

select * from teacher,ClassSession
where Teacher.classCode = ClassSession.classCode  
and ClassSession.classCode = 'A';

because all your original query is returning is everywhere the teacher.classCode matches any given classCode.

Now in terms of removing the duplicate Peter entries that one becomes a bit more difficult, so what you most likely want to do is this:

    select ClassSession.* from teacher,ClassSession
    where Teacher.classCode = ClassSession.classCode  
    and ClassSession.classCode = 'A'  
    and Teacher.name = 'Peter';

now what this does is stops querying the teacher table because you already know whom you are looking for and you supply that as part of the where clause.

After OP update

    select distinct teacher.* from teacher,ClassSession
    where Teacher.classCode = ClassSession.classCode  
    and ClassSession.classCode = 'A';

@FreshPrinceOfSO could you please clarify? it is always beneficial when i can learn something. — Woot4Moo, May 29 '13 at 12:36
[Explicit vs implicit SQL joins](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) — Kermit, May 29 '13 at 12:41

How to prevent duplicate some attributes when query

2 Answers2