SQL Challenge

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

June 27th, 2015 8:58am

And what's wrong with my approach?

June 27th, 2015 11:01am

Good Morning Stefan,

Your valuable suggestion was absolutely fine, and i utilized it generating SSRS report, but unfortunately client emphasis  they are looking for result which shows whether value missing from base or like part. something like

FRONTREAR is not available with Base Part' or

2025 is not available with Like Part MCN452941'

because for a given base part there is n number of like part, and they want to see exactly which like part having difference.

will it be possible to know difference is in base or like part. Please suggest.

Thanks You Very much for valuable help.


  • Edited by Pras05 15 hours 24 minutes ago
Free Windows Admin Tool Kit Click here and download it now
June 27th, 2015 11:57am

You are not comparing on model, so you are missing the model differences, so perhaps

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.Model + ' is not available with Base Part'
 from LikeParts where LikeParts.Model not in (select distinct Model from BaseParts)
 union all 
 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.model + ' is not available with Like Part '+ remarks_exceptions.part
 from baseparts
 join
 (
     select
         allparts.part,
         allremarks.model, allparts.BasePart
     from
         (select distinct basepart, part from Likeparts) allparts
     full join (select basepart, model from baseparts) allremarks on allremarks.BasePart = allparts.BasePart
 except
     select distinct
         part, model, BasePart
     from likeparts
 )remarks_exceptions
 on  remarks_exceptions.BasePart = baseparts.BasePart and remarks_exceptions.model = baseparts.model

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

Tom

  • Marked as answer by Pras05 a few seconds ago
June 27th, 2015 12:41pm

I may be missing something, but it seems that your problem is that you simply are not looking at Make and Model in your query, why you do not catch these differences. So I added these, and I seemed to get the missing rows.

I noticed that I got of duplicates, so I replaced UNION ALL for the BaseParts queries with UNION to get rid of them.

They query looks unwieldy, and I would guess that it can be simplified. As for the performance, I'd rather not think of it.

Anyway, does this give you what you need?

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.Make + ' is not available with Base Part'
 from LikeParts where LikeParts.Make not in (select distinct Make from BaseParts)
 union all
 select *,LikeParts.Model + ' is not available with Base Part'
 from LikeParts where LikeParts.Model not in (select distinct Model from BaseParts)
 union all
 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.Make + ' is not available with Like Part '+ Make_exceptions.part
 from BaseParts
 join
 (
     select
         allparts.part,
         allMake.Make, allparts.BasePart
     from
         (select distinct BasePart, part from LikeParts) allparts
     full join (select BasePart, Make from BaseParts) allMake on allMake.BasePart = allparts.BasePart
 except
     select distinct
         part, Make, BasePart
     from LikeParts
 )Make_exceptions
 on  Make_exceptions.BasePart = BaseParts.BasePart and Make_exceptions.Make = BaseParts.Make

 union

 select BaseParts.*, BaseParts.Model + ' is not available with Like Part '+ Model_exceptions.part
 from BaseParts
 join
 (
     select
         allparts.part,
         allModel.Model, allparts.BasePart
     from
         (select distinct BasePart, part from LikeParts) allparts
     full join (select BasePart, Model from BaseParts) allModel on allModel.BasePart = allparts.BasePart
 except
     select distinct
         part, Model, BasePart
     from LikeParts
 )Model_exceptions
 on  Model_exceptions.BasePart = BaseParts.BasePart and Model_exceptions.Model = BaseParts.Model

 union

 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
      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

     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
     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

Free Windows Admin Tool Kit Click here and download it now
June 27th, 2015 1:16pm

Thank You Tom.
June 28th, 2015 3:44am

Thank You Very Much Erland. You are an inspiration. :)
Free Windows Admin Tool Kit Click here and download it now
June 28th, 2015 3:44am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics