Need some query help

Please help me with the query to select the users which are assigned with both countries like the output below

CodePudding user response：

Using analytic functions one approach might be:

WITH cte AS (
    SELECT *, MIN(country) OVER (PARTITION BY User) AS min_country,
              MAX(country) OVER (PARTITION BY User) AS max_country
    FROM yourTable
)

SELECT User, country
FROM cte
WHERE min_country <> max_country;

This assumes that country would always have a value and that value would always be one of either US or IND.

CodePudding user response：

Steps:

1. Find out users who are having more than one occurence (Assumption is there are no duplicate rows)
2. Apply Group by and Having Clause to achieve result set of step 1
3. Apply a Join with the source table again using User Column and retrieve all the matching rows from the source table

Query

Select A.* from UserDetails A Inner Join ( Select [User] from UserDetails group by [User] having count(*) > 1 ) B On A.[User] = B.[User]

CodePudding user response：

This query might be helful:-

SELECT a.*
FROM TableName a
JOIN (SELECT user, country, COUNT( * )
      FROM TableName 
      GROUP BY user
      HAVING count(*) > 1 ) b
ON a.user = b.user