Is there one self-connection for this scenario?

Question

I have an ALPHA table with 2 fields GroupId, Member:

GroupId | Member;
A1----------A;
A1----------B; 
A1----------C;
A2----------A;
A2----------B;
A3----------A;
A3----------D;
A3----------E;

Objective: . Given the input - A, B, C - I need to query the table to find out if GroupId exists for this exact set of elements. So this is what I plan to do:

• Request a table for all GroupIds whose score is 3 (since my inpt is A, B, C..I knw its 3)
• This will give me A1, A3. Now request this set to match Member.which values ​​exactly will give me A1.

I plan to write a stored procedure and somehow achieve the goal. But, my question can be reached in one request ... maybe one self-join.

Clarification: the set (A, B, C) is unique to A1. And if you give input (A, B, C, D), the query should not return A1.

Answer 1

, , , Member GroupID. , , . , , , , exlucde .

SELECT
   [Alpha].GroupID
FROM
   [Alpha]
GROUP BY
   [Alpha].GroupID
HAVING
       SUM(CASE WHEN [alpha].Member IN ('A','B','C') THEN 1 ELSE 0 END) = 3
   AND MIN(CASE WHEN [alpha].Member IN ('A','B','C') THEN 1 ELSE 0 END) = 1

IN , , ...

SELECT
   [Alpha].GroupID
FROM
   [Alpha]
LEFT JOIN
   [Search]
       ON [Search].Member
GROUP BY
   [Alpha].GroupID
HAVING
       SUM(CASE WHEN [alpha].Member = [search].Member THEN 1 ELSE 0 END) = (SELECT COUNT(*) FROM [search])
   AND MIN(CASE WHEN [alpha].Member = [search].Member THEN 1 ELSE 0 END) = 1

Answer 2

SELECT GroupID
  FROM ALPHA
 WHERE Member IN ('A', 'B', 'C')
 GROUP BY GroupID
HAVING COUNT(*) = 3

, IN () HAVING. SQL , .

---

, , , , A, B, C (, , ) . , " , A, B, C", :

SELECT GroupID
  FROM ALPHA A1
 WHERE Member IN ('A', 'B', 'C')
   AND 3 = (SELECT COUNT(*) FROM ALPHA A2 WHERE A2.GroupID = A1.GroupID)
 GROUP BY GroupID
HAVING COUNT(*) = 3

, 3 A, B C ( , Alpha (GroupID, Member), ).

Answer 3

SELECT DISTINCT aa.GroupId
FROM Alpha aa
JOIN Alpha ab ON (aa.GroupId = ab.GroupId)
JOIN Alpha ac ON (aa.GroupId = ac.GroupId)
LEFT OUTER JOIN Alpha ax ON (aa.GroupId = ax.GroupId AND ax.Member NOT IN ('A', 'B', 'C')
WHERE aa.Member = 'A' AND ab.Member = 'B' AND ac.Member = 'C'
 AND ax.GroupId IS NULL;

GROUP BY, , JOIN . MySQL, , MS SQL Server . , RDBMS.

Answer 4

:

declare @YourTable table (GroupID char(2),Member char(1))

insert into @YourTable values ('A1','A')
insert into @YourTable values ('A1','B')
insert into @YourTable values ('A1','C')
insert into @YourTable values ('A2','A')
insert into @YourTable values ('A2','B')
insert into @YourTable values ('A3','A')
insert into @YourTable values ('A3','D')
insert into @YourTable values ('A3','E')
insert into @YourTable values ('A5','A')
insert into @YourTable values ('A5','B')
insert into @YourTable values ('A5','C')
insert into @YourTable values ('A5','D')
SELECT t1.GroupID
    FROM @YourTable t1
        LEFT OUTER JOIN @YourTable t2 ON t1.GroupID=t2.GroupID AND t2.Member NOT IN ('A', 'B', 'C') 
    WHERE t1.Member IN ('A', 'B', 'C') 
        AND t2.GroupID IS NULL
    GROUP BY t1.GroupID
    HAVING COUNT(*) = 3

OUTPUT:

GroupID
-------
A1

(1 row(s) affected)

:

, "", :

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

, , :

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.


Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/



----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''



RETURN

END --Function FN_ListToTable

:

DECLARE @List varchar(100)
SET @List='A,B,C'
declare @YourTable table (GroupID char(2),Member char(1))

insert into @YourTable values ('A1','A')
insert into @YourTable values ('A1','B')
insert into @YourTable values ('A1','C')
insert into @YourTable values ('A2','A')
insert into @YourTable values ('A2','B')
insert into @YourTable values ('A3','A')
insert into @YourTable values ('A3','D')
insert into @YourTable values ('A3','E')
insert into @YourTable values ('A5','A')
insert into @YourTable values ('A5','B')
insert into @YourTable values ('A5','C')
insert into @YourTable values ('A5','D')

SELECT t1.GroupID
    FROM @YourTable t1
        LEFT OUTER JOIN @YourTable t2 ON t1.GroupID=t2.GroupID AND t2.Member NOT IN (SELECT ListValue FROM dbo.FN_ListToTable(',',@List))
    WHERE t1.Member IN (SELECT ListValue FROM dbo.FN_ListToTable(',',@List))
        AND t2.GroupID IS NULL
    GROUP BY t1.GroupID
    HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.FN_ListToTable(',',@List)) 

:

GroupID
-------
A1

Answer 5

* ALPHA ( ALPHA COUNT (*) = 3)

Answer 6

:

SELECT GroupId
  FROM ALPHA
 GROUP BY GroupId
HAVING SUM(CASE WHEN Member='A' THEN 1.0
                WHEN Member='B' THEN 2.0
                WHEN Member='C' THEN 4.0
                ELSE 7.31415
          END) = 7.0

Answer 7

, , - .

create table #temp(member varchar(10))

create table #groups
(
groupID varchar(2),
member char(1)
)

--#temp holds the members from your delimited string.
--#groups holds your relationships.



select distinct groupID
from #groups
where 
 (select count(*) from #groups i, #temp t
  where i.member = t.member and i.groupID = #groups.groupID) = 
(select count(*) from #temp)