Join on the basis of a value that can present in multiple column

Question

Hi,

I've one source table that contains names and contact numbers for example shown as under.

Name	ContactNumber
Annie	9999555515
Bobby	054321453
Danhy	9090876543
Emma	044367212
A Roomy	034567852

Now i've another table that contains all type of contacts example.

ID	Type Of Contact	Name	Home	Office	Mobile
1	Primary	Annie Roomy	034567852	034567852	9999555515
2	Primary	Bobby	054321452	Null	9899987534
2	Secondary	Bobby	054321452	Null	9899987535
3	Primary	Danhy	9090876543	Null	9090876543
4	Primary	Emma	044367212	044367212	Null

Now i want to find out names based on matching contact number in the above columns.

Suppose for Annie I want to search 9999555515 in all the three columns and if exists i'll list down the names and contact from source table.

Bobby contains two rows of data and both has the matching contact number.

I dont know how to find out this type of records using SQL. 

I can also store temporary result set if required.

Please help.

Answer 1

Sorry untested

SELECT * FROM tbl2 WHERE EXISTS (SELECT * FROM tb1 WHERE tbl2.ContactNumber=tb1.Home OR 

 tbl2.ContactNumber=tb1.Office OR tbl2.ContactNumber=tb1.Mobile)

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

Try

select S.*, T.* from Source S inner join AnotherTable T ON S.ContactNumber IN (Coalesce(T.Home,''), COALESCE(T.Office, ''), COALESCE(T.Mobile,''))

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Try this,

declare @tab1 table
(Name varchar(50),	ContactNumber bigint)
insert into @tab1 values('Annie',	9999555515),
('Bobby',	054321453),
('Danhy',	9090876543),
('Emma',	044367212),
('A Roomy',	034567852)


declare @tab2 table(ID int identity(1,1),	TypeOfContact	varchar(15),Name	varchar(50),Home	bigint,Office	bigint,Mobile bigint)
insert into @tab2 values
('Primary','Annie Roomy',	034567852,	034567852	, 9999555515),
('Primary','Bobby',	054321453,	Null	,9899987534),
('Secondary','Bobby',	054321453,	Null	,9899987535),
('Primary','Danhy',	9090876543	,Null	,9090876543),
('Primary','Emma',	044367212,	044367212	,Null)


select t1.Name Name_1,t2.name Name_2,TypeOfContact, ContactNumber,Home,Office,Mobile from @tab1 t1 inner join @tab2 t2
 on( t1.ContactNumber=t2.Home Or  t1.ContactNumber=t2.Office Or  t1.ContactNumber=t2.Mobile)

Hope this helps

Regards
Satheesh

Answer 4

 Hi,
 
 And if my Source table contains the following.
 
 declare @tab1 table
(Name varchar(50), Home bigint,Office bigint,Mobile bigint)
insert into @tab1 values('Annie', 9999555515, 034567852, 034567852),
('Bobby', 054321453, 9899987534, 9899987534),
('Danhy', 9090876543, 9090876543, 9090876543),
('Emma', 044367212, 044367212, Null),
('A Roomy', 034567852,9999555515, 9999555515 )

And if I want to Look for Home Contact first in the Home, Office and Mobile from the Table 2
then Office in the Home, Office and Mobile from the Table 2
then Mobile in  the Home, Office and Mobile from the Table 2

This means I want If one Contact matches any other Contact in the other columns.

How i can i achieve the same?

Answer 5

Try:

DECLARE @tab1 TABLE (
	NAME VARCHAR(50)
	,Home BIGINT
	,Office BIGINT
	,Mobile BIGINT
	)

INSERT INTO @tab1
VALUES (
	'Annie'
	,9999555515
	,034567852
	,034567852
	)
	,(
	'Bobby'
	,054321453
	,9899987534
	,9899987534
	)
	,(
	'Danhy'
	,9090876543
	,9090876543
	,9090876543
	)
	,(
	'Emma'
	,044367212
	,044367212
	,NULL
	)
	,(
	'A Roomy'
	,034567852
	,9999555515
	,9999555515
	)

DECLARE @tab2 TABLE (
	ID INT
	,[Type Of Contact] VARCHAR(10)
	,[Name] VARCHAR(20)
	,Home BIGINT
	,Office BIGINT
	,Mobile BIGINT
	)

INSERT INTO @tab2
VALUES (
	1
	,'Primary'
	,'Annie Roomy'
	,034567852
	,034567852
	,9999555515
	)
	,(
	2
	,'Primary'
	,'Bobby'
	,054321452
	,NULL
	,9899987534
	)
	,(
	2
	,'Secondary'
	,'Bobby'
	,054321452
	,NULL
	,9899987535
	)
	,(
	3
	,'Primary'
	,'Danhy'
	,9090876543
	,NULL
	,9090876543
	)
	,(
	4
	,'Primary'
	,'Emma'
	,044367212
	,044367212
	,NULL
	)

SELECT T1.*
	,T2.*
FROM @tab1 T1
INNER JOIN @tab2 T2 ON T1.Home IN (
		T2.Home
		,T2.Office
		,T2.Mobile
		)

UNION

SELECT T1.*
	,T2.*
FROM @tab1 T1
INNER JOIN @tab2 T2 ON T1.Office IN (
		T2.Home
		,T2.Office
		,T2.Mobile
		)

UNION

SELECT T1.*
	,T2.*
FROM @tab1 T1
INNER JOIN @tab2 T2 ON T1.Mobile IN (
		T2.Home
		,T2.Office
		,T2.Mobile
		)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Hi Naomi,

In this sample example it works fine but i've real data that contains lakhs of Records.

And searching like that will be very slow.

Is there any other way round.

Answer 7

If you can fix your structure and have one column with phone number and another with the type, then you should be able to index that column and simply join based on the column in both tables. As long as you keep your current structure, all solutions will be similar to what I posted and will not perform too well.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog