22658

SQl server duplicate joins issue with different elements

Question:

Sorry, I am posting again with one more requirement.

can anyone please help: I tried to join with duplicate values but it is not coming as I wanted.

DROP TABLE IF EXISTS #TestTable1 DROP TABLE IF EXISTS #TestTable2 CREATE TABLE #TestTable1 ([No] varchar(50),[Value1] float,[Desc] varchar(50)) insert into #TestTable1 ([No],[Value1],[Desc]) Values (N'123953',300.02,N'Extra Pay') ,(N'123953',427.2,N'Basic Hours') ,(N'123953',106.8,N'Basic Hours') ,(N'123953',213.6,N'Basic Hours') ,(N'123953',213.6,N'Basic Hours') ,(N'123953',213.6,N'Basic Hours') ,(N'123953',213.6,N'Basic Hours') ,(N'123953',105.6,N'Basic Hours') CREATE TABLE #TestTable2 ([No] varchar(50),[Value2] float,[Desc] varchar(50)) insert into #TestTable2 ([No],[Value2],[Desc]) Values (N'123953',200.02,N'Extra Pay') ,(N'123953',553.02,N'Basic Hours') ,(N'123953',446.67,N'Basic Hours') ,(N'123953',427.2,N'Basic Hours') ,(N'123953',106.8,N'Basic Hours') ,(N'123953',213.6,N'Basic Hours') ,(N'123953',213.6,N'Basic Hours') ,(N'123953',213.6,N'Basic Hours') ,(N'123953',105.6,N'Basic Hours')

Desired Output:

[No],[Desc],[Value1],[Value2],[MatchResult] ,(N'123953',N'Extra Pay',300.02,200.02, False) ,(N'123953',N'Basic Hours',427.2,427.2, True) ,(N'123953',N'Basic Hours',106.8,106.8, True) ,(N'123953',N'Basic Hours',213.6,213.6, True) ,(N'123953',N'Basic Hours',213.6,213.6, True) ,(N'123953',N'Basic Hours',213.6,213.6, True) ,(N'123953',N'Basic Hours',213.6,NULL,NULL) ,(N'123953',N'Basic Hours',105.6,105.6, True)

Answer1:

--it looks to me like you should be able to force row_numbers onto duplicate rows, and therfore achieve a one-to-one join, leaving no match on the right as null

SELECT Q1.No, Q1.[desc], Q1.[value1],q2.[value2] FROM (SELECT [NO] , value1, [desc], ROW_NUMBER() over(partition by [NO] , value1, [desc] order by [no]) RN FROM #TestTable1 ) Q1 LEFT JOIN (SELECT [NO] , value2, [desc], ROW_NUMBER() over(partition by [NO] , value2, [desc] order by [no]) RN FROM #TestTable2 ) Q2 ON Q1.Value1=Q2.Value2 AND Q1.[No] = Q2.[NO] AND Q1.[desc] = Q2.[Desc] AND Q1.RN = Q2.rn

Recommend

  • Looping : PhP Array Loops through Database query to output result
  • How to rename xcode project file [duplicate]
  • Save mail with subject as filename
  • PayPal REST API cross reference transaction with payment
  • Braintree Dropin UI does not work with Ionic Framework unless force refresh
  • FIR filter in CUDA (as a 1D convolution)
  • Unique SMS sender id?
  • Why isn't my “Fizz Buzz” test in R working?
  • Python PIL to extract number from image
  • How to skip require in ruby?
  • CodeIgniter URI Parameter is partially bypassing an “if” statement
  • opencv display image without x server
  • jQuery: add elements until a particular height is reached
  • Add dynamic data to line chart from mysql database with highcharts
  • Using Sax parsing to edit and write XML in VB6
  • Reduction and collapse clauses in OMP have some confusing points
  • Adding a button at the bottom of a table view
  • Projection media query: browser support and workarounds?
  • Using $this when not in object context
  • C# - Is there a limit to the size of an httpWebRequest stream?
  • Read text file and split every line in MSBuild
  • PHP - How to update data to MySQL when click a radio button
  • req.body is undefined - nodejs
  • Counter field in MS Access, how to generate?
  • Get object from AWS S3 as a stream
  • Java applet as stand-alone Windows application?
  • MySQL WHERE-condition in procedure ignored
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • Convert array of 8 bytes to signed long in C++
  • Adding custom controls to a full screen movie
  • R: gsub and capture
  • Comma separated Values
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Error creating VM instance in Google Compute Engine
  • Hits per day in Google Big Query
  • Understanding cpu registers
  • how does django model after text[] in postgresql [duplicate]
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices