Sql-server – Find all occurrence of value from column b in column a

sql serversql-server-2008

So basically I have 2 tables which I merge by 

SELECT t2.columnA , t1.columnB, t1.description
FROM Table1 as t1
LEFT JOIN Table2 as t2 on t1.GIDNumber=t2.TwrNumber

I need to display all occurrence of value from t1.columnB in t2.columnA

The perfect situation for this dataset is when there is no duplicated value in column A and value of column B matches value from column A, but i know there is a lot of errors.

This image represent dataset

This is example of situation i want to display. The value of ColumnB which is in red box appears in ColumnA multiple times.

UPDATE: Sample data for first query

t2.columnA 

5900920132278
5900920132278
5900920132278
5900920132278
5900920132278
5900920132278
038548088099
038548088099
038548088099
038548088099
038548088099
5900442707411
5909426907526
5900855046879
4014855166091
5909426909667

t1.columnB

5900920061554
5900920132278
2010000001165
5900920061479
5900920132261
5900920132278
038548088099
5706915071948
38548088099
038548088099
38548088099
2100000038879
2100000035502
2100000030163
4014855026562
2100000031627

In this dataset need to display all records from t2.columnA contains 5900920132278 and 038548088099

UPDATE 2. Data from separate queries:

COLUMN A: 

5900920132278
5900920132278
038548088099
038548088099
038548088099

GIDNumber:

21801
37273
97191
163053
165153

ColumnB:

5900920132278
5900920132278
5900920132278
038548088099
038548088099
038548088099
038548088099

TwrNumber:

21801
37273
91424
21801
37273
91424

Solution :

SELECT t2.columnA , t1.columnB, t1.description
FROM Table1 as t1
LEFT JOIN Table2 as t2 on t1.GIDNumber=t2.TwrNumber
WHERE EXISTS 
( SELECT 1 FROM Table2 as t3 WHERE t1.colmnB = t3.columnA ) 
AND t2.columnA <> t1.columnB

Best Answer

Test Data: 

CREATE TABLE dbo.Table1( columnA nvarchar(255), columnB nvarchar(255),GIDNumber int, Description nvarchar(255));
CREATE TABLE dbo.Table2( columnA nvarchar(255), columnB nvarchar(255),TwrNum int,Description nvarchar(255));

INSERT INTO dbo.Table1(columnA,ColumnB,GIDNumber,Description)
VALUES(5900920132278,5900021321,1,'F-A1120010185')

,(5900920132278,5900021322,1,'F-A1120010165')
,(5900920132278,5900021323,1,'F-A1120010165')
,(5900920132278,5900021324,1,'F-A1120010165');


INSERT INTO dbo.Table2(columnA,ColumnB,TwrNum,Description)
VALUES(2000061232,5900920132278,1,'F-A1120010185');

Original query

SELECT t2.columnA , t1.columnB, t1.description
FROM Table1 as t1
LEFT JOIN Table2 as t2 on t1.GIDNumber=t2.TwrNum;

Result:

columnA columnB description
2000061232  5900021321  F-A1120010185
2000061232  5900021322  F-A1120010165
2000061232  5900021323  F-A1120010165
2000061232  5900021324  F-A1120010165

Adding COUNT() and GROUP BY

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB 
GROUP BY t2.columnB,t2.columnA;

Result:

columnB columnA amount_of_matches
5900920132278   2000061232  4

If you also need to check if GIDNumber = TwrNum 

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB and t1.GIDNumber=t2.TwrNum 
GROUP BY t2.columnB,t2.columnA;

As a result, if column B matches column A but the t1.GIDNumber=t2.TwrNum does not match, it will show as no match:

columnB columnA amount_of_matches
5900920132278   2000061232  4

Extra tests:

INSERT INTO dbo.Table2(columnA,ColumnB,TwrNum,Description)
VALUES(2000061232,5900920132279,2,'F-A1120010185');


SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB 
GROUP BY t2.columnB,t2.columnA;

columnB columnA amount_of_matches
5900920132278   2000061232  4
5900920132279   2000061232  0

INSERT INTO dbo.Table1(columnA,ColumnB,GIDNumber,Description)
VALUES(5900920132278,5900021321,1,'F-A1120010185');

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB 
GROUP BY t2.columnB,t2.columnA;

columnB columnA amount_of_matches
5900920132278   2000061232  4
5900920132279   2000061232  1

Adding the description column:

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches, t2.Description
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB   and t1.GIDNumber=t2.TwrNum 
GROUP BY t2.columnB,t2.columnA, t2.Description;

Result:

columnB columnA amount_of_matches   Description
5900920132278   2000061232  4   F-A1120010185
5900920132279   2000061232  0   F-A1120010185
Related Question