CODEWITHSUNDEEP
sqlsql-server
Muroiwa263
Muroiwa263

Reputation: 33

Return matching rows only SQL Server

I am sure this simple but i am really stuck. Here is a example of the resultset i want from two tables that have the same structure and in this case data or records

TableA

 Ref  cola  colb id
 ------------------
 1     a      b   14
 1     a      b   24

TableB

Ref  cola  colb id
------------------
 1     a      b  1
 1     a      b  2
 1     a      b  3

Expected result:

Ref  cola  colb id Ref1  cola1 colb1 id1
----------------------------------------
1      a     b   14  1      a      b   1
1      a     b   24  1      a      b   2

Upvotes: 0

Views: 121

Answers (3)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Guessing:

;WITH TableAWithRowNum
AS (
SELECT *, ROW_NUMBER(ORDER BY id) AS RowNum
FROM dbo.TableA
), TableBWithRowNum
AS (
SELECT *, ROW_NUMBER(ORDER BY id) AS RowNum
FROM dbo.TableB
)
SELECT a.*, b.*
FROM TableAWithRowNum a 
INNER JOIN TableBWithRowNum b ON a.Ref = b.Ref 
--AND other join predicates on ColA, ... etc.
AND a.RowNum = b.RowNum

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28900

one more way would be 

;with cte
as
(
select Ref, cola,  colb, id,
hashbytes('sha1',concat(Ref, cola,  colb)) as tb1hash
from table1
)
 select 
 t1.Ref, --all required cols
 from cte c
join
(
select Ref, cola,  colb, id,
hashbytes('sha1',concat(Ref, cola,  colb)) as tb2hash
from table2
) b
on
b.tb2hash=c.tb1hash

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

Use:

SELECT *
FROM table1 t1
JOIN Table2 t2
ON t1.Ref =t2.Ref AND t1.cola = t2.cola
   AND t1.colb = t2.colb AND t1.id = t2.id

or

SELECT *
FROM table1 t1
JOIN Table2 t2
USING ( Ref , cola , colb, id )

Upvotes: 2

Related Questions