JOIN based on value of multiple fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

JOIN based on value of multiple fields

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-10-05 : 08:28:02

Table 1

emailAddress
Action

Table 2

Companyname
emailAddress1
emailAddress2
emailAddress3
emailAddress4
emailAddress5

I want to show table 1 with an extra column showing the company name from table 2 where the emailaddress matches either of the 5 email addresses in table 2

Can this be done?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 08:30:42

[code]
Select .... From
Table1 t1 join Table2 t2
on (t1.emailAddress = t2.emailAddress1 or
(t1.emailAddress = t2.emailAddress2 or
(t1.emailAddress = t2.emailAddress3 or
(t1.emailAddress = t2.emailAddress4 or
(t1.emailAddress = t2.emailAddress5)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-05 : 08:44:40

select columns from table1 where emailaddress in
(
	select emailaddress1 from table2  union all
	select emailaddress2 from table2  union all
	select emailaddress3 from table2  union all
	select emailaddress4 from table2  union all
	select emailaddress5 from table2  
)

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 08:56:42

It would be interesting to see how two queries perform.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-05 : 09:50:36

quote:
Originally posted by harsh_athalye

It would be interesting to see how two queries perform.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Yes

Madhivanan

Failing to plan is Planning to fail

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-10-05 : 10:02:34

Thanks for this.

When I run it, if the email address is not in the five email address fields in the second table then that row is missing - is there any way it could be included but with company showing NULL?

Thanks

quote:

Originally posted by harsh_athalye


Select .... From
Table1 t1 join Table2 t2 
on (t1.emailAddress = t2.emailAddress1 or
(t1.emailAddress = t2.emailAddress2 or
(t1.emailAddress = t2.emailAddress3 or
(t1.emailAddress = t2.emailAddress4 or
(t1.emailAddress = t2.emailAddress5)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 10:22:09

Yes change JOIN to LEFT JOIN.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-10-05 : 11:01:13

quote:
Originally posted by harsh_athalye

It would be interesting to see how two queries perform.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Both 7 seconds with about 100,000 rows. No index created yet.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources