Hello Techie.
Greetings!!...
Seeking your help if possible for very strange situation. before describing the problem i would like to summarize table structure.
Table doesn't have KEY On it.
There are two table
Table 1 : it will act like driver and tell attributes of which two value need to compare
Table 2: all comparison and computation done here
/*
logic
1. table 1 contain two column
basepart and likepart
2. i need to check whether all attributes values of basepart are also present with like part in table 2.
if any value present with base part and missing from like part should be in output with message column saying
for eg:- 2016 is not available with Base Part
3. similarly if if any value present with like part and missing from base part should be in output with message column saying
CATCH ME is not available with Like Part MCNW10GR
if there is two difference in a columns of single rows then both should be in output.
The below query is missing resultset when there is missing occur in two column of a single row.
Please suggest what can i change to below query to get the desired result.
for eg:-
The below record is missing from the output.
/*
MCNW10TN 40
8888 2001
FLOORMAT CLASS
FRONTREAR
MCNW10 MCNW10__
8888 is not available with Base Part
(we can see 'FRONTREAR is not available with Base Part' in output but not 8888)
MCN482941 15
222 2016
SLE, CREW front
V335 MCN442941
MCN4_2941
222 is not available with Base Part
(We can see '2016 is not available with Base Part', in the output but not 222 )
MCN442941 A65
B548 2025
SLE, CREW front
V335 MCN442941
MCN4_2941
2025 is not available with Like Part MCN452941
(We can see '2025 is not available with Like Part MCN452941' but not A65 )
MCN442941 A65
B548 2025
SLE, CREW front
V335 MCN442941
MCN4_2941
2025 is not available with Like Part MCN452941
(We can see '2025 is not available with Like Part MCN452941' but not B548 )
*/
Table structure and query
DECLARE @DRIVER TABLE ( BasePart varchar(50), likepart varchar (50) ) insert into @DRIVER select 'MCNW10', 'MCNW10__' insert into @DRIVER select 'MCNW20', 'MCNW20__' insert into @DRIVER select 'MCN442941','MCN4_2941' DECLARE @Parts TABLE ( part varchar(50), Make varchar(50), Model varchar(50), Year varchar(50), remarks varchar(50), remarks2 varchar(50), remarks3 varchar(50) ) insert into @Parts select 'MCNW10','40','353','2001','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10','40','353','2001','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW20','90','555','2015','DOORMAT','CLASS','REAR' insert into @Parts select 'MCNW20','90','555','2015','DOORMAT','CLASS','REAR' insert into @Parts select 'MCNW10','555','111','1975','CATCH ME','CLASS','FRONT' insert into @Parts select 'MCNW10GR','40','353','2001','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10TN','40','8888','2001','FLOORMAT','CLASS','FRONTREAR' INSERT INTO @Parts SELECT 'MCN442941','65','548','2007','SLE, CREW ','front','V335' INSERT INTO @Parts SELECT 'MCN442941','65','548','2007','SLE, CREW ','front','V335' INSERT INTO @Parts SELECT 'MCN442941','A65','B548','2025','SLE, CREW ','front','V335' INSERT INTO @Parts SELECT 'MCN452941','65','548','2007','SLE, CREW ','front','V335' INSERT INTO @Parts SELECT 'MCN452941','65','548','2007','SLE, CREW ', '' ,'V335' INSERT INTO @Parts SELECT 'MCN482941','15','222','2016','SLE, CREW ','front','V335' INSERT INTO @Parts SELECT 'MCN442941','A65','B548','2025','SLE, CREW ','front','V335' ;with LikeParts as ( select parts.part, parts.Make, parts.Model, parts.Year, parts.remarks, parts.remarks2, parts.remarks3, driver.BasePart, driver.likepart from @Parts parts join @Driver driver on parts.part like driver.likepart and parts.part <> driver.BasePart ) ,BaseParts as ( select parts.part, parts.Make, parts.Model, parts.Year, parts.remarks, parts.remarks2, parts.remarks3, driver.BasePart, driver.likepart from @Parts parts join @Driver driver on parts.part = driver.BasePart ) select *,LikeParts.Year + ' is not available with Base Part' from LikeParts where LikeParts.Year not in (select distinct Year from BaseParts) union all select *,LikeParts.remarks + ' is not available with Base Part' from LikeParts where LikeParts.remarks not in (select distinct remarks from BaseParts) union all select *,LikeParts.remarks2 + ' is not available with Base Part' from LikeParts where LikeParts.remarks2 not in (select distinct remarks2 from BaseParts) union all select *,LikeParts.remarks3 + ' is not available with Base Part' from LikeParts where LikeParts.remarks3 not in (select distinct remarks3 from BaseParts) union all select baseparts.*, baseparts.remarks + ' is not available with Like Part '+ remarks_exceptions.part from baseparts join ( select allparts.part, allremarks.remarks, allparts.BasePart from (select distinct basepart, part from Likeparts) allparts full join (select basepart, remarks from baseparts) allremarks on allremarks.BasePart = allparts.BasePart except select distinct part, remarks, BasePart from likeparts )remarks_exceptions on remarks_exceptions.BasePart = baseparts.BasePart and remarks_exceptions.remarks = baseparts.remarks union all select baseparts.*, baseparts.Year + ' is not available with Like Part '+ Year_exceptions.part from baseparts join ( select allparts.part, allYear.Year, allparts.BasePart from (select distinct basepart, part from Likeparts) allparts full join (select basepart, Year from baseparts) allYear on allYear.BasePart = allparts.BasePart except select distinct part, Year, BasePart from likeparts )Year_exceptions on Year_exceptions.BasePart = baseparts.BasePart and Year_exceptions.Year = baseparts.Year union all select baseparts.*, baseparts.remarks2 + ' is not available with Like Part '+ remarks2_exceptions.part from baseparts join ( select allparts.part, allremarks2.remarks2, allparts.BasePart from (select distinct basepart, part from Likeparts) allparts full join (select basepart, remarks2 from baseparts) allremarks2 on allremarks2.BasePart = allparts.BasePart except select distinct part, remarks2, BasePart from likeparts )remarks2_exceptions on remarks2_exceptions.BasePart = baseparts.BasePart and remarks2_exceptions.remarks2 = baseparts.remarks2 union all select baseparts.*, baseparts.remarks3 + ' is not available with Like Part '+ remarks3_exceptions.part from baseparts join ( select allparts.part, allremarks3.remarks3, allparts.BasePart from (select distinct basepart, part from Likeparts) allparts full join (select basepart, remarks3 from baseparts) allremarks3 on allremarks3.BasePart = allparts.BasePart except select distinct part, remarks3, BasePart from likeparts )remarks3_exceptions on remarks3_exceptions.BasePart = baseparts.BasePart and remarks3_exceptions.remarks3 = baseparts.remarks3
Please help me what change i need to made to get desired result.
Thanks