Greetings

I'm brand new in using SQL , so I wish to get help here .

how to find all students names who had taken a course from each professor outside the CS Department ???

.. Any Answer ???

sounds like homework

what's your best attempt so far?

rudy

What have you got so far? What is your train of thought?

This what I got ..

SELECT studid

FROM Transcript, Teaching
WHERE Transcript.crscode = Teaching.crscode And ( SELECT id FROM professor, teaching WHERE professor.id = Teaching.profid AND professor.dept <> 'CS')


???

Here is another attempt :

Select T.Crscode,Trans.stdid.P.Id
From professor p , Teaching T, Transcript Tans
Where p.Iddept <> 'CS'

you need to be a bit more careful in your typing -- sql, like other languages, is very unforgiving about typos

you have a period between Trans.stdid and P.Id in the SELECT clause, and it should be a comma

also, you alias the Transcript table to Tans but then use Trans in the SELECT list

you said the problem called for student names, so you still need to join to the student table, and then use only SELECT DISTINCT student.name because the same student will take multiple classes from professors that aren't 'CS" but you want each student name to show up only once

Hi Rudy ,

Thanx a lot for your notes
Here is My correction :


SELECT DISTINCT s.name
From Student S,
Where
( Select T.Crscode, Trans.stdid, P.Id
From professor p , Teaching T, Transcript Trans
Where p.Iddept <> 'CS'
)

Am I right now ???

nope, even more diligence is required

there's a comma in front of WHERE

the subquery inside the parentheses will return an entire result set of rows, and this cannot be a condition of WHERE

you need to join the student table to the other tables


rudy

OK


SELECT DISTINCT s.name
From Student S
Where
( Select T.Crscode, Trans.stdid, P.Id
From professor p , Teaching T, Transcript Trans
Where p.Iddept <> 'CS'
)

What about the Join ?? How can I use it here ??

you still have that syntax problem in the WHERE clause

the WHERE clause needs to have one or more conditions that evealuate true or false

a subselect consisting of a result set of rows is not a condition

see Getting the Right Data with SQL Joins

AHA


SELECT DISTINCT s.name
From Student S, Teaching T, professor p

Where T.profid = P.Id AND
( Select T.Crscode, Trans.stdid, P.Id
From professor p , Teaching T, Transcript Trans
Where p.Iddept <> 'CS'
)

you have a cross-join there -- in order to make it an inner join, you will have to add join conditions, e.g. S.teachid=T.id

and you still have an invalid WHERE condition

i'm through for this morning, i have to go to work now

Thanx for the link Rudy , This is my last try and I think it will be more accurate:

SELECT DISTINCT s.name
From Student S, Teaching T, professor p

Where (T.profid = P.Id) AND (T.studid = S.id) And
( Select T.Crscode, Trans.stdid, P.Id
From professor p , Teaching T, Transcript Trans
Where p.Iddept <> 'CS'
)

Thanx alot Rudy And have a nice time