Sanjai Kummar Sanjai Kummar - 3 months ago 14
MySQL Question

mysql How to Find Difference between two rows in same table and list the Unmatched Records? mysql finding unmatched rows in a table

In MySQL i want to find difference between the two rows in same table
where i need to display the unmatched records

Here is my Table named

project_details


CREATE TABLE IF NOT EXISTS `project_details` (
`project_name` varchar(100) NOT NULL,
`project_detail` varchar(100) NOT NULL,
`project_version` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `project_details` (`project_name`, `project_detail`, `project_version`) VALUES
('project_1', 'php_mysql', '1.0'),
('project_1', 'php_mysql_android', '2.0'),
('project_1', 'php_mysql_android_ajax', '3.0'),
('project_2', 'php_mysql', '1.0'),
('project_2', 'php_mysql_android', '2.0'),
('project_2', 'php_mysql_android_ajax', '3.0'),
('project_2', 'php_mysql_jquery', '1.0'),
('project_2', 'php_mysql', '4.0');


This is how table looks like

enter image description here

where i want the result of unmatched records from the table
that is from comparing project_1 and project_2

need to Compare both project_1 and project_2 from project_name then it should get the unmatched records of project_detail and project_version

i need result something like this displaying only unmatched records refer below
pic

enter image description here

Answer

Use NOT EXISTS to retrieve output where for different project names there are no matching rows based on detail and version:

select *
from project_details p1
where not exists (
  select 1
  from project_details p2
  where p1.project_name <> p2.project_name
    and p1.project_detail = p2.project_detail
    and p1.project_version = p2.project_version
  )

SQL Fiddle to see how it works live.

Output

project_name    project_detail      project_version
---------------------------------------------------
project_2       php_mysql_jquery    1.0
project_2       php_mysql           4.0
Comments