I have two tables shown below. Now I just need to search and match of Table A with Table B and identify the exact matching record set from Table B.
Like: Search the Name, Age and Sex values of Table A into Table B and Identify the exact matching records of TableB.
As here Only 301 & 501 are the exactly same as 101 record of Table A.
Thanks in advance.
Note: I had already asked a question on this link. But both questions are different.
You can try the below query. Here I have used a simple query shown below to get all the records that exist in both tables.
SELECT DISTINCT DataSetId AS SearchId, FindinValues FROM ( Select TableA.*, TableB.DataSetId as FindinValues, ROW_NUMBER() OVER (PARTITION By TableB.DataSetId Order By TableB.DataSetId) RN from TableA inner join TableB on TableA.ColumnA = TableB.ColumnA and TableA.ColumnB = TableB.ColumnB )a where RN = (Select Count(*) from tableA) The output of the above query is as shown below.
SearchId FindinValues ------------------------ 101 301 101 501 Now to get the above table into comma separated group by search id you have two choices in terms of simplicity i.e., you can consider your above query as a table or you can insert the above query records into a temporary table and run the below query on that.
-- To get the data in comma separated which exists in both the table. SELECT SearchId, FindinValues = STUFF((SELECT ', ' + Cast(FindinValues as Varchar(20)) FROM (SELECT DISTINCT DataSetId AS SearchId, FindinValues FROM ( Select TableA.*, TableB.DataSetId as FindinValues, ROW_NUMBER() OVER (PARTITION By TableB.DataSetId Order By TableB.DataSetId) RN from TableA inner join TableB on TableA.ColumnA = TableB.ColumnA and TableA.ColumnB = TableB.ColumnB )a where RN = (Select Count(*) from tableA)) b WHERE b.SearchId = a.SearchId FOR XML PATH('')), 1, 2, '') FROM (SELECT DISTINCT DataSetId AS SearchId, FindinValues FROM ( Select TableA.*, TableB.DataSetId as FindinValues, ROW_NUMBER() OVER (PARTITION By TableB.DataSetId Order By TableB.DataSetId) RN from TableA inner join TableB on TableA.ColumnA = TableB.ColumnA and TableA.ColumnB = TableB.ColumnB )a where RN = (Select Count(*) from tableA)) a GROUP BY SearchId This will give output as shown below.
SearchId FindinValues ------------------------ 101 301, 501 You can find the live demo here.
Here is another way with a shorter code.
SELECT DISTINCT DataSetId AS SearchId, FindinValues into #TempResult FROM ( Select TableA.*, TableB.DataSetId as FindinValues, ROW_NUMBER() OVER (PARTITION By TableB.DataSetId Order By TableB.DataSetId) RN from TableA inner join TableB on TableA.ColumnA = TableB.ColumnA and TableA.ColumnB = TableB.ColumnB )a where RN = (Select Count(*) from tableA) -- To get the data in comma separated which exists in both the table. Select * from #TempResult SELECT SearchId, FindinValues = STUFF((SELECT ', ' + Cast(FindinValues as Varchar(10)) FROM #TempResult b WHERE b.SearchId = a.SearchId FOR XML PATH('')), 1, 2, '') FROM #TempResult a GROUP BY SearchId You can find this demo here. This will run in the lower version of SQL Server also.
You can get the matches using join and aggregation:
select a.datasetid, b.datasetid from a join b on a.columna = b.columna and a.columnb = b.columnb group by a.datasetid, b.datasetid having count(*) = (select count(*) from a a2 where a2.datasetid = a.datasetid); Note: This returns as matches even when b has additional rows. That makes sense to me. Also, this assumes that a given columna only appears once for each data set id (in either table).
This gives the values in separate rows. You can re-aggregate to get your final result:
select datasetid_a, string_agg(datasetid_b, ',') from (select a.datasetid as datasetid_a, b.datasetid as datasetid_b from a join b on a.columna = b.columna and a.columnb = b.columnb group by a.datasetid, b.datasetid having count(*) = (select count(*) from a a2 where a2.datasetid = a.datasetid) ) ab group by datasetid_a; string_agg() is not available in older versions of SQL Server. To be honest, in those versions, I would stick with the results in separate rows.
