Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1873
Members Online:
3
Guests Online:
45

Total Post History
Posts:
81445
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
BitogClogs

Other

FAQ
All times are UTC [ DST ]

Google Ads

REALLY CRITICAL

Microsoft SQL Server
Please wait...

REALLY CRITICAL

Postby Vivan on Thu May 28, 2015 1:42 pm

Hello Techie,

May anyone guide me to design sql query for this critical scenario.

logic

1. table 1 contain two column
basepart and likepart

2. i need to check whether all attributes of basepart are same with attributes of 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
'x' value change in respective column

3. similarly if if any value present with like part and missing from base part should be in output with message column saying
'x' value not available with base part in respective column

if there is two difference in a columns of single rows then output consists of two records.

  1. declare @Driver table
  2. (
  3. BasePart varchar(50),
  4. likepart varchar (50)
  5. )
  6.  
  7. insert into @Driver select 'MCNW10','MCNW10__'
  8.  
  9. insert into @Driver select 'MCNW20','MCNW20__'
  10.  
  11.  
  12. insert into @Driver select 'MCN442941','MCN4_2941'
  13.  
  14.  
  15.  
  16. --SELECT * FROM @Driver
  17.  
  18. declare @Parts table
  19. (
  20. part varchar(50),
  21. Make varchar(50),
  22. Model varchar(50),
  23. Year varchar(50),
  24. remarks varchar(50),
  25. remarks2 varchar(50),
  26. remarks3 varchar(50)
  27. )
  28.  
  29. insert into @Parts select 'MCNW10','40','353','2001','FLOORMAT','CLASS','FRONT'
  30. insert into @Parts select 'MCNW10','40','353','2001','FLOORMAT','CLASS','FRONT'
  31. insert into @Parts select 'MCNW20','90','555','2015','DOORMAT','CLASS','REAR'
  32. insert into @Parts select 'MCNW20','90','555','2015','DOORMAT','CLASS','REAR'
  33. insert into @Parts select 'MCNW10','555','111','1975','CATCH ME','CLASS','FRONT'
  34. insert into @Parts select 'MCNW10GR','40','353','2001','FLOORMAT','CLASS','FRONT'
  35. insert into @Parts select 'MCNW10TN','40','8888','2001','FLOORMAT','CLASS','FRONTREAR'
  36.  
  37.  
  38.  
  39. INSERT INTO @Parts SELECT 'MCN442941','65','548','2007','SLE, CREW ','front','V335'
  40. INSERT INTO @Parts SELECT 'MCN442941','65','548','2007','SLE, CREW ','front','V335'
  41. INSERT INTO @Parts SELECT 'MCN442941','A65','B548','2025','SLE, CREW ','front','V335'
  42. INSERT INTO @Parts SELECT 'MCN452941','65','548','2007','SLE, CREW ','front','V335'
  43. INSERT INTO @Parts SELECT 'MCN452941','65','548','2007','SLE, CREW ',  ''   ,'V335'
  44. INSERT INTO @Parts SELECT 'MCN482941','15','222','2016','SLE, CREW ','front','V335'
  45. INSERT INTO @Parts SELECT 'MCN442941','A65','B548','2025','SLE, CREW ','front','V335'
  46.  


Expected Output
/*
part ,Make ,Model ,Year ,remarks remarks2 remarks3 BasePart likepart (comment)
'MCNW10TN','40', '8888','2001','FLOORMAT', 'CLASS', 'FRONTREAR', MCNW10, MCNW10TN, 8888 model is not available with base part MCNW10
'MCNW10TN','40', '8888','2001','FLOORMAT', 'CLASS', 'FRONTREAR', MCNW10, MCNW10TN, FRONTREAR Remarks3 is not available with base part MCNW10
'MCNW10' ,'555' ,'111','1975','CATCH ME' ,'CLASS' ,'FRONT' , MCNW10, MCNW10GR, Catch me is not available with like part MCNW10GR
'MCNW10' ,'555' ,'111','1975','CATCH ME' ,'CLASS' ,'FRONT' , MCNW10, MCNW10TN, Catch me is not available with like part MCNW10TN
'MCN452941','65', '548','2007','SLE, CREW ', '', 'V335' , MCN442941, MCN452941, '' remarks2 is not available with base part MCN442941
'MCN482941','15', '222','2016','SLE, CREW ', 'front', 'V335', MCN442941, MCN452941, Make 15 is not available with basepart MCN442941
'MCN482941','15', '222','2016','SLE, CREW ', 'front', 'V335', MCN442941, MCN452941, Model 222 is not available with basepart MCN442941
'MCN482941','15', '222','2016','SLE, CREW ', 'front', 'V335', MCN442941, MCN452941, Year 2016 is not available with basepart MCN442941
'MCN442941','A65', 'B548','2025','SLE, CREW ', 'front', 'V335', MCN442941, MCN452941, MAKE A65 is not present with like part MCN452941
'MCN442941','A65', 'B548','2025','SLE, CREW ', 'front', 'V335', MCN442941, MCN452941, Model B548 is not present with like part MCN452941
'MCN442941','A65', 'B548','2025','SLE, CREW ', 'front', 'V335', MCN442941 MCN452941, year 2025 is not present with like part MCN452941
'MCN442941','A65', 'B548','2025','SLE, CREW ', 'front', 'V335', MCN442941 MCN482941, MAKE A65 is not present with like part MCN482941
'MCN442941','A65', 'B548','2025','SLE, CREW ', 'front', 'V335', MCN442941 MCN482941 Model B548 is not present with like part MCN482941
'MCN442941','A65', 'B548','2025','SLE, CREW ', 'front', 'V335', MCN442941 MCN482941, year 2025 is not present with like part MCN482941


*/

Thanks a lot.
Vivan
Newbie
Newbie
 
Posts: 1
Joined: Thu May 28, 2015 1:02 pm
Unrated