Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unanswered: Simple SQL Query

    Supplier(Sno, Sname)
    Part ( Pno, Pname )
    Project (Jno, Jname)
    Supply (Sno, Pno, Jno)

    I need to write a query to:

    retrieve the part numbers that are supplied to exactly to exactly two projects

    what I wrote was:

    SELECT Pno FROM Supply WHERE (SELECT COUNT(Pno) from Supply)=2;

    I do however think this is wrong as when I run a similar query on a table I have, it returns an empty set ... I essentially just want to pull the elements that their are 2 of in a specified column.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your subquery counts the overall number of rows in the supply table

    so unless there are exactly two rows in that table, the outer query returns nada, squadoosh, no results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    2
    I have worked out the answer:

    SELECT Pno FROM Supply
    GROUP by Pno
    HAVING (COUNT (Pno) = 2)

    thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, that's it

    although, as a matter of coding style, the parentheses are unnecessary, and of course since Pno is part of that table's primary key, it can't be null, so you can just count rows instead of counting non-null values...

    HAVING COUNT(*) = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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