CODEWITHSUNDEEP
mysqlsqlcomparerows
user8428395
user8428395

Reputation:

SQL compare rows with displaying differences in columns in the same table

Hello im looking for a query in SQL or MySQl about comparing rows in the same table

That query should display 2 last rows with the same id's but with different records in columns

My Table

ID|is_superuser|username|first_name|last_name|email          |is_staff|is_active|
 1|           1|admin   |FC        |         |[email protected]|       1|        1|
 1|           1|admin   |          |         |[email protected]|       1|        1|
 1|           1|admin   |adminname |         |[email protected] |       1|        1|

RESULT :

ID|username|first_name|email|
 1|admin   |adminname |[email protected]|
 1|admin   |          |[email protected]|

THX For Help

Upvotes: 0

Views: 857

Answers (1)

Andomar
Andomar

Reputation: 238058

You could use an exists clause to demand that there is another row with the same id but a different email:

select  *
from    MyTable t1
where   exists
        (
        select  *
        from    MyTable t2
        where   t1.id = t2.id
                and coalesce(t1.email, '') <> coalesce(t2.email, '')
        )

Per your comment, if you have many columns, you can generate the where clause with a query like:

select  concat('and coalesce(t1.', column_name, ', '''') <> coalesce(t2.', 
               column_name, ', '''')')
from    information_schema.columns
where   table_name = 'MyTable'

Upvotes: 1

Related Questions