Find the users having more than two elements and one of those elements must be A

Question

I want to extract the users having more than two elements and one of those elements must be A.

This my table:

CREATE TABLE #myTable(
    ID_element nvarchar(30),
    Element nvarchar(10),
    ID_client nvarchar(20)
)

This is the data of my table:

INSERT INTO #myTable VALUES
(13 ,'A', 1),(14 ,'B', 1),(15 ,NULL, 1),(16 ,NULL, 1),
(17 ,NULL, 1),(18 ,NULL, 1),(19 ,NULL, 1),(7, 'A', 2),
(8, 'B', 2),(9, 'C', 2),(10 ,'D', 2),(11 ,'F', 2),
(12 ,'G', 2),(1, 'A', 3),(2, 'B', 3),(3, 'C', 3),
(4, 'D', 3),(5, 'F', 3),(6, 'G', 3),(20 ,'Z', 4),
(22 ,'R', 4),(23 ,'D', 4),(24 ,'F', 5),(25 ,'G', 5),
(21 ,'x', 5)

And this is my query:

Select Distinct  ID_client
from #myTable
Group by ID_client
Having      Count(Element) > 2

Your task description is unclear. You need to show all the records and their users that are equal to A as long as User have more then 2 records? Or show users that have more then two records and at least one of them is A? Or... — przemo_li, Oct 18 '16 at 17:09
Hi przemo thanks for your reply, i want to show users that have more then two records and at least one of them is A — Ilyas, Oct 18 '16 at 17:17

score 2 · Accepted Answer · answered Oct 18 '16 at 17:40

Add to your query CROSS APPLY with id_clients that have element A

SELECT m.ID_client
FROM #myTable m
CROSS APPLY (
    SELECT ID_client
    FROM #myTable
    WHERE ID_client = m.ID_client
        AND Element = 'A'
    ) s
GROUP BY m.ID_client
HAVING COUNT(DISTINCT m.Element) > 2

Output:

ID_client
2
3

score 0 · Answer 2 · edited Oct 18 '16 at 18:26

0

I think this is what you are looking for:

SELECT * FROM 
(SELECT *, RANK() OVER (PARTITION BY element ORDER by id_client) AS grouped FROM #myTable)  t 
wHERE grouped > 1
AND Element = 'A'
ORDER by t.element

which brings back

ID_element  Element ID_client   grouped
7   A   2   2
1   A   3   3

edited Oct 18 '16 at 18:26

Andrew Morton

24,203
9
60
84

answered Oct 18 '16 at 18:18

Lakshmi raghu

1
1

score 0 · Answer 3 · edited May 23 '17 at 11:48

You can select the ID_client values which have an 'A' as an Element and join your table with the result of that:

SELECT m.ID_Client
FROM #myTable AS m
JOIN (
      SELECT a.ID_Client FROM #myTable AS a
      WHERE a.Element = 'A') AS filteredClients
ON m.ID_client = filteredClients.ID_client
GROUP BY m.ID_client
HAVING COUNT(m.Element) > 2

Outputs:

ID_Client
2
3

However, this is not necessarily the best way to do it: When should I use Cross Apply over Inner Join?

Find the users having more than two elements and one of those elements must be A

3 Answers3