CODEWITHSUNDEEP
mysqlsql
snarf
snarf

Reputation: 55

Query to match different values in one column that have same value in a separate column

I am trying to write a query that will take two different values in column b and then compare it with column c to determine if the two values in column b share the same value in Column C. However i also need column A in the output as well.

For example

Column A         Column B        Column C
Test 1           x               12345
Test 2           y               12345
Test 3`          A               12344
Test 4           D               12342

Desired Output

Column A        Column B        Column C
Test 1           x              12345
Test 2           y              12345

Any help would be great

Upvotes: 0

Views: 1283

Answers (3)

Rahul
Rahul

Reputation: 77866

You can perform a JOIN like below. See a demo fiddle http://sqlfiddle.com/#!9/da525/4

select t.*
from tbl1 t join tbl1 s on t.`Column C` = s.`Column C`
and t.`Column B` <> s.`Column B`;

(OR) Using WHERE EXISTS

select t.*
from tbl1 t
where exists ( select 1 from tbl1 
              where `Column C` = t.`Column C`
              and `Column B` <> t.`Column B`);

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

I'm not sure if the values in ColumnB are significant. This query finds values of ColumnC that are repeated and then returns those rows:

select * from T where ColumnC in (
    select ColumnC from T
    group by ColumnC
    having count(*) > 1 /* or maybe count(distinct ColumnB) > 1 */
)

Upvotes: 1

Nir-Z
Nir-Z

Reputation: 869

try this

SELECT a.* FROM table a join table b on a.c=b.c and a.b<>b.b

The query doesn't take into account rows that have same values in c and b column. You can add DISTINCT in the select if needed.

Upvotes: 0

Related Questions