current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a task at hand which requires me to return the details of a student who is enrolled in a class taught by a teacher with the surname of Hoffman and I'm stuck.

    SELECT * FROM Public."Class" WHERE tid=(
        SELECT tid FROM Public."Tutor" WHERE tname LIKE '%Hoffman');

This returns to me the classes taught by Hoffman but from here I'm not sure where to go. I believe I have to access the 'Enrolled' table and then finally the student table but have tried to no avail. The following query is as far as I got before breaking the query -_- I'm sure i'll have to use the HAVING or IN keyword but I don't quite know what to do with them!

SELECT * FROM Public."Student" WHERE programme='IT' (
    SELECT * FROM Public."Class" WHERE tid=(
        SELECT tid FROM Public."Tutor" WHERE tname LIKE '%Hoffman')
    );

Any help would be much appreciated!

The database structures are as follows:-

Student(sid integer, sname varchar(20), programme varchar(4), level integer, age integer) 
Class(ccode varchar(6), cname varchar(25), week_day varchar(3), meets_at time, room 
varchar(6), tid integer) 
Enrolled(sid integer, ccode varchar(6)) 
Tutor(tid integer, tname varchar(20))

Thanks again :)

Update:-

SELECT DISTINCT *
FROM Public."Student" s
INNER JOIN Public."Enrolled" e ON e.sid = s.sid
INNER JOIN Public."Class" c ON c.ccode = e.ccode
INNER JOIN Public."Tutor" t ON t.tid = c.tid
WHERE programme='IT' AND t.tname LIKE '%Hoffman';
share|improve this question
2  
The Pg tutorial is well worth a look: postgresql.org/docs/current/static/tutorial.html . (Not being snarky, just making an honest suggestion for someone getting started with SQL). –  Craig Ringer Dec 8 '13 at 13:06

3 Answers 3

up vote 1 down vote accepted

The two solutions above will result in students to be reported multiple times if they are enrolled in multiple classes from the same teacher. If the only goal of the query is to select students only once, the query below will do exactly that.

SELECT *
FROM Student s
WHERE s.programme = 'IT'
AND EXISTS (
  SELECT * 
  FROM Enrolled e
  JOIN Class c ON c.ccode = e.ccode
  JOIN Tutor t ON t.tid = c.tid
  WHERE e.sid = s.sid
  AND t.tname LIKE '%Hoffman'
  );
share|improve this answer
    
I'm using DISTINCT. Is this incorrect? I'll update post to show current query –  Timmy Dec 8 '13 at 11:58
1  
The whole point is: you don't need distinct. The students in the student table are already unique. (I presume). If you don't generate duplicates you don't have to exclude them. –  wildplasser Dec 8 '13 at 12:16
up vote 4 down vote

You don't need to do subqueries for each validation. This could easily be done with JOINS:

SELECT s.*
FROM Student s
INNER JOIN Enrolled e ON e.sid = s.sid
INNER JOIN Class c ON c.ccode = e.ccode
INNER JOIN Tutor t ON t.tid = c.tid
WHERE t.tname LIKE '%Hoffman';
share|improve this answer
    
Thank you very much, I can only accept one answer and went with @Miguelo's answer as he included the programme limitation but this seems equally as acceptable! –  Timmy Dec 8 '13 at 11:24
    
@Timmy. No problem. I wasn't sure if that was a requirement or not, and because you didn't mention it in your description, i didn't include it. –  Filipe Silva Dec 8 '13 at 11:26
up vote 2 down vote

You can use joins to solve this instead of subquery

SELECT * FROM Public."Student"  s
join Public.Enrolled e on (s.sid= e.id)
join Public.Class c on (c.ccode = e.ccode)
join Public.Tutor t on (c.tid = t.tid)
WHERE s.programme='IT' and  t.tname like  '%Hoffman'
share|improve this answer
    
Thank you very much! –  Timmy Dec 8 '13 at 11:22
    
@Timmy Also, a correct subquery based query would probably just get transformed into the above join-based query by the optimizer anyway. It prefers to flatten away subqueries when it can. In general you only need subqueries when you can't do what you need with joins. –  Craig Ringer Dec 8 '13 at 13:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.