Common scenario: finding other attributes associated with foreign keys  

  • Common scenario:

    • We have a query that find the qualifying foreign keys (e.g.: CID = 2342)

    • We need to find other attributes associated with that key (e.g.: CourseNo)

  Example: finding other attributes associated with foreign keys  

  • Find fname and lname of all employees who has a dependent

    Answer: getting the SSN of employees with dependents is easy 

     Dependent relation:

    +-----------+-----------+------+-----------+--------------+
    | essn      | name      | sex  | bdate     | relationship |
    +-----------+-----------+------+-----------+--------------+
    | 333445555 | Alice     | F    | 05-APR-76 | DAUGHTER     |
    | 333445555 | Theodore  | M    | 25-OCT-73 | SON          |
    | 333445555 | Joy       | F    | 03-MAY-48 | SPOUSE       |
    | 987654321 | Abner     | M    | 29-FEB-32 | SPOUSE       |
    | 123456789 | Micheal   | M    | 01-JAN-78 | SON          |
    | 123456789 | Alice     | F    | 31-DEC-78 | DAUGHTER     |
    | 123456789 | Elizabeth | F    | 05-MAY-57 | SPOUSE       |
    +-----------+-----------+------+-----------+--------------+

 SSN of employees who has dependents:
 
     333445555   987654321   123456789

  Example: finding other attributes associated with foreign keys  

  • Find fname and lname of all employees who has a dependent

    Query that find the SSN of employees with dependents: 

       select essn
   from   dependent    

       +-----------+
       | essn      |
       +-----------+
       | 333445555 |
       | 333445555 |
       | 333445555 |
       | 987654321 |
       | 123456789 |
       | 123456789 |
       | 123456789 |
       +-----------+

  Example: finding other attributes associated with foreign keys  

  • Find fname and lname of all employees who has a dependent

    You can use a subquery to obtain the other attributes associated with these keys: 

       select essn     
   from   dependent   

       +-----------+
       | essn      |
       +-----------+
       | 333445555 |
       | 333445555 |
       | 333445555 |
       | 987654321 |
       | 123456789 |
       | 123456789 |
       | 123456789 |
       +-----------+

    select  fname, lname
    from    employee
    where   ssn IN (select essn     
                    from   dependent         
                   )