1

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
  • There are duplicates in table t2 as you can see. @ypercubeᵀᴹ – Zackk Dec 12 '18 at 11:32
  • No, I can't see. Please add data from separate queries: SELECT * FROM t1 WHERE columnB = 5900920132278; and SELECT * FROM t2 WHERE columnA = 5900920132278; A few rows are enough but be sure to include all related columns (columnA, columnB, GIDNumber, TwrNumber) – ypercubeᵀᴹ Dec 12 '18 at 11:35
  • ... and desured output on THAT source data. – Akina Dec 12 '18 at 11:36
  • GIDNumber and twrNumber are primary keys, Connection condition are imposed by database's documentation. @ypercubeᵀᴹ – Zackk Dec 12 '18 at 12:12
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.