Results 1 to 15 of 15

Thread: Query in SQL

  1. #1
    Join Date
    Sep 2003
    Posts
    15

    Cool Unanswered: Query in SQL

    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 ???
    Last edited by SAl; 09-29-03 at 15:08.

  2. #2
    Join Date
    Sep 2003
    Posts
    15
    .. Any Answer ???

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sounds like homework

    what's your best attempt so far?

    rudy

  4. #4
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    7
    What have you got so far? What is your train of thought?

  5. #5
    Join Date
    Sep 2003
    Posts
    15
    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')


    ???
    Last edited by SAl; 10-06-03 at 03:39.

  6. #6
    Join Date
    Sep 2003
    Posts
    15
    Here is another attempt :

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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2003
    Posts
    15
    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 ???

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  10. #10
    Join Date
    Sep 2003
    Posts
    15
    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 ??

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  12. #12
    Join Date
    Sep 2003
    Posts
    15
    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'
    )

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  14. #14
    Join Date
    Sep 2003
    Posts
    15
    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'
    )

  15. #15
    Join Date
    Sep 2003
    Posts
    15
    Thanx alot Rudy And have a nice time

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •