---

Trick to get other attributes using a set of foreign key with nested query

---

• Getting other attributes with a set of foreign keys
  • Trick:

    If you have a set of foreign keys S Then you can get the other attributes with the foreign keys as follows: select other-attributes from relation where key in (foreign keys S)

    ---

    ---

    ---

    ---

  • Example:

    Find fname and lname of all employees who has a dependent

    Solution:

    The set of foreign keys of employees with a dependent is: select essn from dependent Result: +-----------+ | essn | +-----------+ | 333445555 | | 333445555 | | 333445555 | | 987654321 | | 123456789 | | 123456789 | | 123456789 | +-----------+ To find the fname and lname of these employees: select fname, lname from employee where ssn IN (select essn from dependent ) Result: +-----------+--------+---------+ | ssn | fname | lname | +-----------+--------+---------+ | 123456789 | John | Smith | | 333445555 | Frankl | Wong | | 987654321 | Jennif | Wallace | +-----------+--------+---------+ (I included SSN for clarity)