0

I have simple parent -> 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.

0
2

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 > 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) > 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;
3
  • I thought this was it, but unfortunately I had to change the LIKE to in IN clause, which breaks it: t.text in ('include1', 'include2') the photo must have all tags – mxmissile Sep 3 '20 at 21:13
  • 1
    Whatever the case, even with the IN clause, I can see no reason that wouldn't work. Can you post some sample data? – Mike Petri Sep 3 '20 at 22:34
  • If this works, can you mark it as the answer please? – Mike Petri Sep 8 '20 at 15:41
1

Sorry I have found I mistyped "intersect" as "union", all are corrected now

Previous problem

(select photo_id from tags where text = 'include-1'
intersect
select photo_id from tags where text = '2-some-tag'
intersect
select photo_id from tags where text = 'anothertag'
)
except
select photo_id from tags where text in ('exclude-x', 'dont-include')

New problem

select parent_id from @children where attribute = 'red hair'
intersect 
select parent_id from @children where attribute = 'brown eyes'
2
  • Thanks, this does not work though, a photo_id must have all the the given tags. – mxmissile Sep 4 '20 at 13:45
  • Sorry I have found I mistyped "intersect" as "union", all are corrected now – redsimon Sep 4 '20 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.