quote:

---

Originally posted by pinecrest515

Dear Forumers,

I figured out that my previous SQL statement, which I thought solved a problem, doesn't:

Suppose I have the following table:

First_Name Last_Name Age_Group Vaccinated Age
John Smith Adult Yes 30
Mary Smith Child Yes 5
Casey Smith Child No 12
Joe Smith Child No 6
Sam Walton Adult Yes 31
Thomas Walton Child No 15
John Walton Child Yes 5
Mary Scott Adult Yes 56
Andrew Johnson Adult Yes 55
Bryon Johnson Child No 12


I am trying to query for a list of Adults that have all their children (who have same last name) either Vaccinated or over the age of 10.

Ideally, the query should output the following:
First_Name Last_Name Age_Group Vaccinated Age
Sam Walton Adult Yes 31
Andrew Johnson Adult Yes 55

Sam Walton is returned, because he has 1 child over 15 and 1 child vaccinated.
Andrew Johnson is returned, because although his child is not vaccinated, the child is over 10 years old.
John Smith is not returned, because although 2 of his children are either vaccinated or over 10, the third child is not vaccinated and under 10.
Mary Scott is not returned, because she has no children.


Currently, my query finds a list of Adults that have at least one child vaccinated or one child over 10. If an adult has one child under 10 who is not vaccinated, but has another child over 10 or vaccinated, then the query will still return the adult's record. This is not robust - I would like only adults who have all their children either vaccinated or over 10.


My current, un-robust query:
SELECT DISTINCT
A.*
FROM
[Table1] AS [A]
INNER JOIN
[Table1] AS [B]
ON [A].[Last_Name] = [B].[Last_Name]
WHERE
[A].[Age_Group] = 'Adult'
AND [B].[Age_Group] = 'Child'
AND
([B].[Vaccinated] = 'Yes' OR [B].[Age] > 10 )

Currently, my un-robust query is outputting the following:
First_Name Last_Name Age_Group Vaccinated Age
Sam Walton Adult Yes 31
John Smith Adult Yes 30
Andrew Johnson Adult Yes 55



How should I best construct this query to achieve the desired results?

Thanks in advance for your reply!

---

SELECT A.*
FROM Table1 A
	JOIN
	(
		SELECT C.Last_Name
		FROM Table1 C
		WHERE C.Age_Group = 'Child'
		GROUP BY C.Last_Name
		HAVING COUNT(*) = SUM(CASE WHEN C.Vaccinated = 'Yes' OR C.Age > 10 THEN 1 ELSE 0 END)
	) D
		ON A.Last_Name = D.Last_Name
WHERE A.Age_Group = 'Adult'

DECLARE	@Sample TABLE
	(
		FirstName VARCHAR(20),
		LastName VARCHAR(20),
		AgeGroup VARCHAR(20),
		Vaccinated VARCHAR(3),
		Age TINYINT
	)

INSERT	@Sample
	(
		FirstName,
		LastName,
		AgeGroup,
		Vaccinated,
		Age
	)
SELECT	'John',   'Smith',   'Adult', 'Yes', 30 UNION ALL
SELECT	'Mary',   'Smith',   'Child', 'Yes',  5 UNION ALL
SELECT	'Casey',  'Smith',   'Child', 'No',  12 UNION ALL
SELECT	'Joe',    'Smith',   'Child', 'No',   6 UNION ALL
SELECT	'Sam',    'Walton',  'Adult', 'Yes', 31 UNION ALL
SELECT	'Thomas', 'Walton',  'Child', 'No',  15 UNION ALL
SELECT	'John',   'Walton',  'Child', 'Yes',  5 UNION ALL
SELECT	'Mary',   'Scott',   'Adult', 'Yes', 56 UNION ALL
SELECT	'Andrew', 'Johnson', 'Adult', 'Yes', 55 UNION ALL
SELECT	'Bryon',  'Johnson', 'Child', 'No' , 12

-- Ifor
SELECT		a.*
FROM		@Sample AS a
INNER JOIN	(
			SELECT		LastName
			FROM		@Sample
			WHERE		AgeGroup = 'Child'
			GROUP BY	LastName
			HAVING		COUNT(*) = SUM(CASE WHEN Vaccinated = 'Yes' OR Age > 10 THEN 1 ELSE 0 END)
		) AS d ON d.LastName = a.LastName
WHERE		a.AgeGroup = 'Adult'

-- Peso
SELECT	FirstName,
	LastName,
	AgeGroup,
	Vaccinated,
	Age
FROM	(
		SELECT	FirstName,
			LastName,
			AgeGroup,
			Vaccinated,
			Age,
			MIN(CASE WHEN AgeGroup = 'Adult' THEN 2 WHEN Vaccinated = 'Yes' OR Age > 10 THEN 1 ELSE 0 END) OVER (PARTITION BY LastName) AS Value
		FROM	@Sample
	) AS d
WHERE	AgeGroup = 'Adult'
	AND Value = 1