CODEWITHSUNDEEP
mysqlsql
Subash
Subash

Reputation: 7266

sql query to find unique combination of column data

I am trying to write sql query that will select distinct combination of columns from a table.

Example table data:

+--+---+---+---+
|id|fk1|fk2|fk3|
+--+---+---+---+
|1 |1  |2  |3  |
+--+---+---+---+
|2 |1  |3  |3  |
+--+---+---+---+
|3 |2  |2  |3  |
+--+---+---+---+
|4 |2  |4  |3  |
+--+---+---+---+
|5 |3  |2  |3  |
+--+---+---+---+
|6 |3  |3  |3  |
+--+---+---+---+
|7 |3  |4  |3  |
+--+---+---+---+

Here I need to write a query that will return fk1 => 2 as it has unique combination of fk2 => 2 and 4

Please let me know if the question is not clear.

Thanks

Upvotes: 0

Views: 510

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I see. The values "2" and "4" are inputs into the query and you want exactly that combination. This is a set-within-sets query, and I think aggregation with a having clause is the most flexible approach.

select fk1
from table t
group by fk1
having sum(fk2 = 2) > 0 and
       sum(fk2 = 4) > 0 and
       sum(fk2 not in (2, 4)) = 0;

That is, the key fk1 has the value of 2 (at least once), has the value of 4, and has no other values.

Upvotes: 1

Related Questions