In SQL, omit entire record if column (of multiple values) matches condition

Question

I'm pretty new to SQL... I have a table with the following columns :

Employee,Title,Age,Children

Output of a basic SELECT would be :

Steve |Foreman|40|Billy
Steve |Foreman|40|Amy
Steve |Foreman|40|Michelle
Daniel|Smith  |35|Eric
Daniel|Smith  |35|Jake
Erin  |Otis   |29|Eileen

Hopefully, I've shown that each record can contain multiple children. What I'd like to be able to do is to only return values if the Employee doesn't have a child who's name starts with 'E'. Right now I'm still returning Employees but it only lists the records that don't have a child starting with 'E'. I want to completely omit the Employee if any of their children start with an 'E' not just omit the child starting with 'E'.

Is this possible?

Thanks.

EDIT :

So in actuality there are two tables, one for EMPLOYEES and one for CHILDREN. So my current query looks like this :

SELECT E.EMPLOYEE_NAME, E.EMPLOYEE_TITLE, E.EMPLOYEE_AGE, C.CHILDREN_NAME
  FROM EMPLOYEE E INNER JOIN CHILDREN C ON E.EMPLOYEE_ID = C.EMPLOYEE_ID
 WHERE C.CHILDREN_NAME NOT LIKE 'E%'

This returns all rows minus any children that have a name starting with E. The desired effect, is solution 2 that Trinimon provided; do not return an employee if any of their children have a name that start with E.

I'm hoping that explains it a bit more and someone can explain how to produce the desired results. As mentioned, Trinimon's solution returns the proper results but since there are two tables I'm not how to adjust the solution to my schema.

Thanks.

Mike Christensen · Answer 1 · 2013-09-26T19:27:15.850

You could try:

select * from YourTable T
where NOT EXISTS (select 1 from YourTable where Employee=T.Employee and Children like 'E%')

This, of course, will have a problem if your have two employees with the same name. You could expand the WHERE clause to cover all the attributes that make an employee the same:

select * from YourTable T
where NOT EXISTS (select 1 from YourTable where Employee=T.Employee and Title=T.Title and Age=T.Age and Children like 'E%')

However, you should consider making Children a separate table. Have a single Employee (with a unique EmployeeID) in your table, and have Children contain each child with a reference to EmployeeID.

Sorry for the delay in response... I appreciate the response. I'll expound a bit in a new comment. — user2820853, Oct 03 '13 at 18:47

Trinimon · Accepted Answer · 2013-10-03T20:21:26.493

1

Either go for ...

SELECT * 
  FROM employees
 WHERE Children NOT LIKE 'E%';

if you want all records where the child's name doesn't start with E or for ...

SELECT * 
  FROM employees e1
 WHERE NOT EXISTS (SELECT 1 
                     FROM employees e2
                    WHERE e1.Employee = e2.Employee
                      AND Children LIKE 'E%');

if none of the returned employees should have a child that starts with E.

Check out version one and version two.

p.s. based on your structure it's

SELECT E.EMPLOYEE_NAME, 
       E.EMPLOYEE_TITLE,
       E.EMPLOYEE_AGE, 
       C.CHILDREN_NAME
  FROM EMPLOYEE E INNER JOIN CHILDREN C ON E.EMPLOYEE_ID = C.EMPLOYEE_ID
 WHERE NOT EXISTS (SELECT 1 
                     FROM CHILDREN C2
                    WHERE E.EMPLOYEE_ID = C2.EMPLOYEE_ID
                      AND C2.CHILDREN_NAME LIKE 'E%');

Check this Fiddle.

edited Oct 03 '13 at 20:21

answered Sep 26 '13 at 19:29

Trinimon

13,839
9
44
60

Version two produces the desired results... I've edited my original post to provide a better explanation of the schema. I hope that provides enough insight for some assistance. – user2820853 Oct 03 '13 at 19:02
Pretty much the same principle - see my update ... :) – Trinimon Oct 03 '13 at 20:22
That did indeed work, Trinimon. Thanks! For learning purposes, NOT EXISTS is saying, return the data opposite of the query, correct? I tried adding another AND statement to the subquery to remove another data set (LIKE 'B%') but when I run that it returns the entire table instead of nothing. What am I missing? Also why SELECT 1 instead of SELECT *? Thanks again. – user2820853 Oct 04 '13 at 12:51
I believe I found how to make it work: AND (C2.CHILDREN_NAME LIKE 'E%' OR C2.CHILDREN_NAME LIKE 'B%')); This returns the desired data set but why is it necessary to write like that instead of using multiple AND statements? – user2820853 Oct 04 '13 at 15:06
If you write `NOT EXISTS (SELECT ... AND C2.CHILDREN_NAME LIKE 'E%' AND C2.CHILDREN.NAME = 'B%')`, the result of the subselect is always `null` because a name can't start with `E` and `B` at the same time. This is exactly what fulfills the `NOT EXISTS` clause. If you want to exclude all employees who have children that start either wit `B`or with `E`, then use `OR` instead. – Trinimon Oct 04 '13 at 20:59

score 0 · Answer 3 · answered Sep 26 '13 at 19:42

0

Here is a mysql solution in sql fiddle. I second, that you normalize your data and make a child table.

http://sqlfiddle.com/#!2/f52cc/8/0

answered Sep 26 '13 at 19:42

ui_90jax

759
2
6
17

In SQL, omit entire record if column (of multiple values) matches condition

3 Answers3