Sanjai Kummar Sanjai Kummar - 1 year ago 155
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


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

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

enter image description here

Answer Source

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.


project_name    project_detail      project_version
project_2       php_mysql_jquery    1.0
project_2       php_mysql           4.0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download