SQL Query to match and increment the number on the match

Transact-SQL
1

can anyone help with the the query please? i am having hard time with it...

create table #Table1
(PK int
,SomeNumber int
,MatchOnNumber int
)

INSERT INTO #Table1 VALUES(100,111,99999 )
INSERT INTO #Table1 VALUES(100,111,88888)
INSERT INTO #Table1 VALUES(100,111,77777)
INSERT INTO #Table1 VALUES(100,111,66666 )

INSERT INTO #Table1 VALUES(200,222,44444 )
INSERT INTO #Table1 VALUES(200,222,33333)
INSERT INTO #Table1 VALUES(200,222,22222)

Create Table #Table2
(MatchNumber1 int
,matchNumber2 int
,MatchNumber3 int
)

INSERT INTO #Table2 VALUES (66666,77777,88888)
INSERT INTO #Table2 VALUES (44444,33333,22222)

--- desired output:
Create Table #DesiredOutput
(
PK int
,SomeNumber int
,MatchOnNumber int
,RowNumber INT
)

INSERT INTO #DesiredOutput VALUES(100,111,99999,1)
INSERT INTO #DesiredOutput VALUES(100,111,88888,1)
INSERT INTO #DesiredOutput VALUES(100,111,77777,1)
INSERT INTO #DesiredOutput VALUES(200,222,44444,2)
INSERT INTO #DesiredOutput VALUES(200,222,33333,2)
INSERT INTO #DesiredOutput VALUES(200,222,22222,2)

SELECT * FROM #DesiredOutput

2

hi

please see SQL .. i have removed the # ..i have used permanent tables ..

select 
   * 
from 
  Table1
where 
  MatchOnNumber in 
(
	select MatchNumber1 from Table2 
		union all 
	select MatchNumber2 from Table2 
		union all 
	select MatchNumber3 from Table2 
)

image
image
image

3

I'm confused.

How did 99999 get a match?

Where did the row number on the match come from? Is that supposed to be the row number in the #table2? If so, you'll need to explicitly add a row number / identity to table 2, since SQL Server doesn't guarantee any specific row order when reading rows from a table.

4

Scott

It "could be" a mistake from poster ..

I am assuming he hard coded the output .. and made a typo mistake ..

happens to me all the time .. i have to always check the accuracy part after i type .. ( my issues )
( thought errors .. aberrations .. which happen to everybody )

5

that is correct ...it was a typo @ScottPletcher and thanks @harishgg1 for the code. but is there anyway that we can get the rowNumber as output? not sure if it is possible....i tried and no luck at all...

6

hi ..

getting the rowNumber as output .. looks very very easy ..

but what is rowNuimber ???

7

thats the tricky part...the rownumber OR maybe call it as Dense_RANK should be based on table2 and SomeNumber in table1...does it makes sense?

8

all that can be done !! no problem

what column ( or columns ) in table 2 and SomeNumber in table1
what is the thing in your mind .. how it should work !!!

doing it is easy .. just need to know whats in your mind
for tips and tricks Scott and 2 Jeffs many seniors are there in this forum

9

Change the #table2 definition to include an identity from which to generate a rownumber, then the query is below:

CREATE TABLE #Table2
(
 ID int IDENTITY(1, 1) NOT NULL 
,MatchNumber1 int
,matchNumber2 int
,MatchNumber3 int
)

SELECT t1.*, t2.RowNumber
FROM #Table1 t1
INNER JOIN (
    SELECT t2.RowNumber, ca1.MatchNumber
    FROM (
        SELECT *, ROW_NUMBER() OVER(ORDER BY ID) AS RowNumber 
        FROM #Table2 t2
    ) AS t2
    CROSS APPLY ( VALUES(MatchNumber1),(MatchNumber2),(MatchNumber3) ) AS ca1(MatchNumber)
) AS t2 ON t2.MatchNumber = t1.MatchOnNumber
10

@ScottPletcher results are perfect. but would be great if we get the results only if everything matches in table2. for example: if i insert another record in table2 as below:
INSERT INTO #Table2 VALUES (44444,33333,11111)

i should still get the same result.

11 
SELECT t1.*, t2.RowNumber
FROM #Table1 t1
CROSS APPLY (
    SELECT TOP (1) t2.RowNumber, ca1.MatchNumber
    FROM (
        SELECT *, ROW_NUMBER() OVER(ORDER BY ID) AS RowNumber 
        FROM #Table2 t2
    ) AS t2
    CROSS APPLY ( VALUES(MatchNumber1),(MatchNumber2),(MatchNumber3) ) AS ca1(MatchNumber)
    WHERE ca1.MatchNumber = t1.MatchOnNumber
    ORDER BY t2.RowNumber
) AS t2
12

these are great. thank you so much everyone