CODEWITHSUNDEEP
sqlsql-servert-sql
Ilyas
Ilyas

Reputation: 153

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

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

Upvotes: 0

Views: 143

Answers (3)

Andrew Morton
Andrew Morton

Reputation: 25013

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?

Upvotes: 0

Lakshmi raghu
Lakshmi raghu

Reputation: 1

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

Upvotes: 0

gofr1
gofr1

Reputation: 15977

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

Upvotes: 2

Related Questions