T-SQL query must include and exclude child rows I have simple parent -&gt; child (one to many) relationship. Need to return children that have all given attributes, in this case red hair and brown eyes.
Table:
CREATE TABLE [dbo].[children](
    [child_id] [int] IDENTITY(1,1) NOT NULL,
    [parent_id] [int] NOT NULL,
    [attribute] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK_children] PRIMARY KEY CLUSTERED 
(
    [child_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Data:
insert into children (parent_id, attribute) values (1, 'red hair');
insert into children (parent_id, attribute) values (1, 'blue eyes');
insert into children (parent_id, attribute) values (2, 'red hair');
insert into children (parent_id, attribute) values (2, 'brown eyes');
insert into children (parent_id, attribute) values (3, 'purple hair');
insert into children (parent_id, attribute) values (3, 'brown eyes');

Desired Result:
parent_id
2

Semi Working Query:
select parent_id

from children

where attribute in ('red hair','brown eyes')

group by parent_id

having count(child_id) = 2;

That query works, however I would think there is a better way, (maybe not?) where I don't have to provide a count of the given arguments.
Update: Previous question and example were overly complicated and not communicated well. Hopefully this simplifies the issue.
Alright, round three!
So, it looks like you're trying to find any parents that match all given criteria, correct? I'm assuming you're looking to use this in some real world scenario (a report or application). If that's the case, you'll need to pass in the criteria using a parameter.
The code here is going on that assumption.For this to work correctly, you'll need a function to split the parameter string. STRING_SPLIT function is not supported until SQL 2012, so if you're working in 2008, you'll need this function.
SET QUOTED_IDENTIFIER ON;

SET ANSI_NULLS ON;
GO

CREATE FUNCTION dbo.SplitString
(
    -- Add the parameters for the function here
    @myString     VARCHAR(500)
    ,@deliminator VARCHAR(10)
)
RETURNS @ReturnTable TABLE
(
    -- Add the column definitions for the TABLE variable here
    id     INT         IDENTITY(1, 1) NOT NULL
    ,value VARCHAR(50) NULL
)
AS
BEGIN
    DECLARE @iSpaces INT;
    DECLARE @part VARCHAR(50);

    --initialize spaces
    SELECT  @iSpaces = CHARINDEX(@deliminator, @myString, 0);

    WHILE @iSpaces &gt; 0
    BEGIN
        SELECT  @part = SUBSTRING(@myString, 0, CHARINDEX(@deliminator, @myString, 0));

        INSERT INTO @ReturnTable
        (
            value
        )
        SELECT @part ;

        SELECT  @myString = SUBSTRING(
                                         @myString, CHARINDEX(@deliminator, @myString, 0) + LEN(@deliminator)
                                         ,LEN(@myString) - CHARINDEX(' ', @myString, 0)
                                     );

        SELECT  @iSpaces = CHARINDEX(@deliminator, @myString, 0);
    END;

    IF LEN(@myString) &gt; 0 INSERT INTO @ReturnTable   SELECT @myString;

    RETURN;
END;
GO

Then, this query should give you the results you're looking for.
If you're using SQL 2012 or higher, you can replace the function (dbo.SplitString) with the built in windowed function STRING_SPLIT and achieve the same results.
DECLARE @ParmList VARCHAR(MAX) = 'red hair,blue eyes';

SELECT  c.parent_id
FROM    dbo.children AS c
WHERE   EXISTS (
                   SELECT   1
                   FROM     dbo.SplitString(@ParmList, ',') AS at
                   WHERE    at.value = c.attribute
               )
GROUP BY
        c.parent_id;