Date: 09/22/09 (MySQL Communtiy)    Keywords: mysql, database, sql

I found an instruction set that said to list the first, and last name of all employees that had neither 'SON' nor 'DAUGHTER' listed in their dependency files. I came up with a query that gave me the results I wanted by assuming the database only takes SPOUSE, SON, and DAUGHTER, but assuming has always caused me trouble, so I would like to know: Is there any better way I could have gone about approaching this so it's not going by a general assumption of dep_relation = 1, and so forth?

mysql> SELECT * FROM dependent;
+-------------+------------+------------+-------------------+------------------+
| dep_emp_ssn | dep_name   | dep_gender | dep_date_of_birth | dep_relationship |
+-------------+------------+------------+-------------------+------------------+
| 999444444   | Jo Ellen   | F          | 1996-04-05        | DAUGHTER         | 
| 999444444   | Andrew     | M          | 1998-10-25        | SON              | 
| 999444444   | Susan      | F          | 1975-05-03        | SPOUSE           | 
| 999555555   | Allen      | M          | 1968-02-29        | SPOUSE           | 
| 999111111   | Jeffery    | M          | 1978-01-01        | SON              | 
| 999111111   | Deanna     | F          | 1978-12-31        | DAUGHTER         | 
| 999111111   | Mary Ellen | F          | 1957-05-05        | SPOUSE           | 
+-------------+------------+------------+-------------------+------------------+


mysql> SELECT * FROM employee;
+-----------+---------------+----------------+
| emp_ssn   | emp_last_name | emp_first_name |
+-----------+---------------+----------------+
| 999666666 | Bordoloi      | Bijoy          | 
| 999555555 | Joyner        | Suzanne        | 
| 999444444 | Zhu           | Waiman         | 
| 999887777 | Markis        | Marcia         | 
| 999222222 | Amin          | Hyder          | 
| 999111111 | Bock          | Douglas        | 
| 999333333 | Joshi         | Dinesh         | 
| 999888888 | Prescott      | Sherri         | 
+-----------+---------------+----------------+



What I generated was:

SELECT emp_first_name, emp_last_name 
FROM 
	(
	SELECT emp_first_name, emp_last_name,
	dep_relationship, COUNT(dep_relationship) AS count 
	FROM dependent 
	JOIN employee ON dep_emp_ssn = emp_ssn 
	GROUP BY emp_ssn
	) tempTable 
WHERE tempTable.count = 1 
AND tempTable.dep_relationship != 'SON' 
AND tempTable.dep_relationship != 'DAUGHTER';

To get:

+----------------+---------------+
| emp_first_name | emp_last_name |
+----------------+---------------+
| Suzanne        | Joyner        | 
+----------------+---------------+

Source: http://mysql.livejournal.com/137636.html