CODEWITHSUNDEEP
sqlsql-server
DenseCrab
DenseCrab

Reputation: 1313

SQL Query group by column, where other column is different

Ok sample data from table Users:

ThisId  ThatId
1       5
1       5
1       6
2       7
3       8
3       8
4       9

First i get the records with duplicate ThisId, so: 

SELECT
    ThisId
FROM
    Users
GROUP BY
    ThisId
HAVING 
    COUNT(*) > 1

This works and returns

ThisId
1
3

However I only want the records where the ThatId is different. So my output should be only:

ThisId
1

Because the records with ThisId 1 have different ThatIds (5,6). And the records with ThisId 3 all have Thatid 8.

I'm pretty much stuck here and don't know how to achieve this.

I hope you understand, thanks in advance

Upvotes: 2

Views: 81

Answers (3)

PeterH
PeterH

Reputation: 1040

Really simple but would this work ?

SELECT
    ThisId
FROM
    Users
GROUP BY
    ThisId
HAVING 
    COUNT(*) > 1
    AND COUNT(DISTINCT ThatId) > 1

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

Here is the answer.

SELECT
    ThisId
FROM
    Users
GROUP BY
    ThisId
HAVING 
    MAX(ThatId) <> MIN (ThatId)

Result:

ThisId
-----------
1

Upvotes: 1

Rams
Rams

Reputation: 2169

Below query should work

SELECT
    ThisId
FROM
    Users
GROUP BY
    ThisId
HAVING 
    COUNT(distinct ThatId) > 1

Upvotes: 4

Related Questions