0

If a table "XYZ" contains two columns as combine primary key, I need to select records that contain both Value1 and Value2 in COLUMN_B ( Result: ID4 )

I tried with "IN" query select COLUMN_A from XYZ where COLUMN_B in ('VALUE1', 'VALUE2'); but it returns the records which contain VALUE1 OR VALUE2 in COLUMN_B (ID1, ID2, ID3, ID4).

Can anybody please help me to write a MySQL query that can do this.

COLUMN_A    COLUMN_B

ID1         Value1
ID2         Value1
ID3         Value2
ID4         Value1
ID4         Value2
ID5         Value3
ID3         Value3
Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Dineshkani
  • 2,899
  • 7
  • 31
  • 43

2 Answers2

4

This is a messy way of doing it on a very large table, but it will accomplish the goal:

SELECT COLUMN_A
  FROM xyz
 WHERE COLUMN_B IN ('Value1', 'Value2')
 GROUP BY COLUMN_A
 HAVING COUNT(DISTINCT COLUMN_B) = 2;

This counts the number of distinct COLUMN_B values for each COLUMN_A record and returns only the records that have both Value1 and Value2. Note that this will not exclude COLUMN_A records that have additional values, such as Value3 and Value4. That would require a different sort of query that would need to take the size of the source table into consideration ...

matigo
  • 1,321
  • 1
  • 6
  • 16
1

I should test this first before suggesting, but what about:

SELECT DISTINCT tableA.column_A  
FROM XYZ AS tableA, XYZ AS tableB
WHERE tableA.column_A = tableB.column_A
    AND tableA.column_B = 'Value1'
    AND tableB.column_B = 'Value2'
Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32
Julien Lamarche
  • 931
  • 1
  • 12
  • 29