CODEWITHSUNDEEP
sql
user1759228
user1759228

Reputation: 143

comparing rows in same table

I am having a result from a query which looks as below

+-------------------+
| id | c1 | c2 | c3 |
+-------------------+
| 1  | x  | y  | z  |
+----+----+----+----+
| 1  | x  | y  | z1 |
+----+----+----+----+
| 2  | a  | b  | c  |
+----+----+----+----+
| 2  | a1 | b  | c1 |
+-------------------+

I need to fetch only records which have values in C1 and c2 different for the same id.

For the above example the result should be 

+-------------------+
| id | c1 | c2 | c3 |
+-------------------+
| 2  | a  | b  | c  |
+----+----+----+----+
| 2  | a1 | b  | c1 |
+-------------------+

Can you please help with the query.

Upvotes: 0

Views: 75

Answers (4)

Filipe Roxo
Filipe Roxo

Reputation: 722

This will give you any row which is different of any other. However in the case you have 3 rows, it might not work as you want.

SELECT t1.*
FROM someTable t1, someTable t2
WHERE t1.id = t2.id
   AND  (t1.c1 != t2.c1 OR t1.c2 != t2.c2)

Edit:

If you want only rows that are different of any other row with the same id the first query won't work in this case:

+-------------------+
| id | c1 | c2 | c3 |
+-------------------+
| 1  | x  | y  | z  |
+----+----+----+----+
| 1  | x  | y  | z1 |
+----+----+----+----+
| 2  | a  | b  | c  |
+----+----+----+----+
| 2  | a1 | b  | c1 |
+----+----+----+----+
| 2  | a1 | b  | c3 |
+-------------------+

You would get:

+-------------------+
| id | c1 | c2 | c3 |
+-------------------+
| 2  | a  | b  | c  |
+----+----+----+----+
| 2  | a1 | b  | c1 |
+----+----+----+----+
| 2  | a1 | b  | c3 |
+-------------------+

Which I think would be wrong. In that case you will need something like:

SELECT t2.*
FROM
(
    SELECT id, c1, c2
    FROM someTable
    GROUP BY id, c1, c2
    HAVING COUNT(*) = 1
) t1
JOIN someTable t2 ON t2.id = t1.id 
        AND t2.c1 = t1.c1
        AND t2.c2 = t1.c2

Upvotes: 3

ZakiMak
ZakiMak

Reputation: 2102

If I understood the question correctly I think the answer of @MattGibson is correct, but if the order matters then order by clause will be required (order by id).

Upvotes: 0

Matt Gibson
Matt Gibson

Reputation: 38238

Joining the table to itself should work. I'm assuming that you meant C1 or C2 being different, given the example result you posted.

SELECT 
  t1.id,
  t1.c1,
  t1.c2,
  t1.c3
FROM
  your_table t1 
      INNER JOIN your_table t2 ON t1.id = t2.id
WHERE
  t1.c1 <> t2.c1 OR
  t1.c2 <> t2.c2

SQLFiddle.

Upvotes: 4

ericpap
ericpap

Reputation: 2937

Try this:

SELECT * FROM myTable WHERE ID 
IN(SELECT ID FROM myTable GROUP BY ID HAVING COUNT(ID)>1)

Upvotes: -2

Related Questions