Sanjai Kummar Sanjai Kummar - 3 months ago 29
SQL Question

mysql find the difference between two rows in same table and display only the second row difference not the first row

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 by comparing both rows in a same table with query below

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
)


successfully iam getting the output difference of project_1 and project_2 some thing like this

enter image description here

now if i change the records in the table like this

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_ajaxs', '4.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');


where records changed table looks like this

enter image description here

with the same comparing query like above ,comparing project_1 and project_2 iam able to get output like this

enter image description here

but here i want to display only project_2 difference alone. i dont want show project_1 difference

more simply : i want to compare both the rows of project_1 and project_2 but i want to display only project_2 difference alone no need to display project_1 difference information

That is I need to show some thing like comparing with project_1 what all are difference found in project_2


i need query for this , thanks in advance

Answer

What you need is just an additional condition:

p1.project_name = 'project_2'

http://sqlfiddle.com/#!9/035126/4

But you can do it better and select all version differences between 'project_1' and 'project_2':

select pd.project_detail, 
  p1.project_version as old_version,
  p2.project_version as new_version
from(
  select distinct project_detail
  from project_details
  where project_name in ('project_1', 'project_2')
) pd
left join project_details p1
  on  p1.project_name = 'project_1'
  and p1.project_detail = pd.project_detail
left join project_details p2
  on  p2.project_name = 'project_2'
  and p2.project_detail = pd.project_detail
where not (p1.project_version <=> p2.project_version)

The result will be something like:

|         project_detail | old_version | new_version |
|------------------------|-------------|-------------|
| php_mysql_android_ajax |         4.0 |         3.0 |
|              php_mysql |         1.0 |         4.0 |
|    somthing_depricated |         1.0 |      (null) |
|       php_mysql_jquery |      (null) |         1.0 |

http://sqlfiddle.com/#!9/f9005c/1

Comments