CODEWITHSUNDEEP
mysql
LionKing_MB
LionKing_MB

Reputation: 31

MySQL query to unique combination of columns

Table-

TestID | IntegID
=======|======
P1     | In1
P2     | In2
P2     | In2
P3     | In3
P1     | In5
P1     | In5
P2     | In2
P3     | In3
P2     | In9

Output desired:

TestID | IntegID
=======|======
P1     | In1
P1     | In5
P2     | In2
P2     | In9

So basically I want to extract rows only if there is one-is-to-many relationship between column 1 and column 2.

Like P3 only had 1 corresponding value in column 2, so it should not be extracted. However, since P1 and P2 had multiple values in column 2, the associated rows were extracted.

Can you please help me with a query in MySql to extract the above output.

Upvotes: 1

Views: 54

Answers (2)

You Old Fool
You Old Fool

Reputation: 22959

You can join the table to itself to get distinct IntegID columns and then use DISTINCT to remove duplicate rows like this:

SELECT DISTINCT t1.TestID, t1.IntegID
FROM my_table t1
JOIN my_table t2 ON t2.TestID = t1.TestID AND t2.IntegID != t1.IntegID;

I think the above approach is probably the simplest and will give you the best performance but I like scaisEdge's answer above using COUNT(DISTINCT... so here's yet another approach using IN and selecting only the TestID rows you need:

SELECT DISTINCT TestID, IntegID
FROM my_table
WHERE TestID IN
(
    SELECT TestID
    FROM my_table
    GROUP BY TestID
    HAVING COUNT(DISTINCT IntegID) > 1
)

Sometimes it's nice to try a few approaches and see what works best with your indexes and other aspects of your application. Cheers.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

You can use a subquery with count(distinct IntegId) in join 

SELECT DISTINCT my_table.TestId, my_table.IntegId
FROM my_table 
INNER JOIN (
  SELECT TestID, COUNT(DISTINCT IntegID) 
  FROM my_table
  GROUP BY TestId 
  HAVING COUNT(DISTINCT IntegID) > 1
) T ON T.TestID = my_table.TestID

Upvotes: 2

Related Questions