Select rows having 2 columns equal value

Question

Consider following table

C1    || C2  || C3  || C4
--------------------------
1     || a   || b   || 1
2     || a   || b   || 4
3     || b   || d   || 2
4     || b   || d   || 2

Question 1: Select all rows in which Column C2 , C3, C4 have equal values e.g. select row 3 and row 4 in above example.

Question 2 : Select all rows in which C4 column has duplicates e.g. C4 has value 2 in row 3 and row 4, so select row 3 and 4.

Answer 1

Question 1 query:

SELECT ta.C1
      ,ta.C2
      ,ta.C3
      ,ta.C4
FROM [TableA] ta
WHERE (SELECT COUNT(*)
       FROM [TableA] ta2
       WHERE ta.C2=ta2.C2
       AND ta.C3=ta2.C3
       AND ta.C4=ta2.C4)>1

Answer 2

Actually this would go faster in most of cases:

SELECT *
FROM table ta1
JOIN table ta2 on ta1.id != ta2.id
WHERE ta1.c2 = ta2.c2 and ta1.c3 = ta2.c3 and ta1.c4 = ta2.c4

You join on different rows which have the same values. I think it should work. Correct me if I'm wrong.

Answer 3

Select * from tablename t1, tablename t2, tablename t3 
where t1.C1 = t2.c2 and t2.c2 = t3.c3 

Seems like this will work. Though does not seems like an efficient way.

Answer 4

SELECT *
FROM my_table
WHERE column_a <=> column_b AND column_a <=> column_c