Complex Query (a join?) - SQL Server Forums

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)

Complex Query (a join?)

Author

Topic

jlyles
Starting Member

2 Posts

Posted - 2009-06-16 : 18:03:06

Here's the scenario. I've got two tables, one with quite a bit of data in it, and one with some additional contact data. I'll refer to the first table as DataTable and the second as ExtraDataTable.

They look something like this


DataTable
num	title		         email			phone
1	title1		bob@aol.com		   555-555-1234
2	title2		jim@aol.com		   NULL
3	title3		steve@aol.com	   NULL
4	title4		tim@aol.com		   NULL
5	title5		james@aol.com	   555-555-2345

ExtraDataTable
email			phone			frequency
sue@aol.com		555-555-5556		2
sue@aol.com		555-555-5555		1
jim@aol.com		555-123-1234		2
jim@aol.com		555-123-2345		1
jim@aol.com		555-123-3456		1
steve@aol.com	555-999-9999		2
tim@aol.com		555-111-1111		1
tim@aol.com		555-222-2222		2
tim@aol.com		555-333-3333		3

As you can see, ExtraDataTable, holds additional phone numbers and their frequency, along with a corrosponding email address.

I'm trying to develop a query that will return num, title, email, and phone from DataTable if the record has a phone. If the particular record doesnt have a phone, I need it to pull the phone number from ExtraDataTable that has the highest frequency.

So the result would look like this.


Result
num	title	 	       email			phone
1	title1		bob@aol.com		      555-555-1234
2	title2		jim@aol.com		      555-123-1234
3	title3		steve@aol.com		555-999-9999
4	title4		tim@aol.com		      555-333-3333
5	title5		james@aol.com		555-555-2345

I've spent quite a bit of time trying to figure this out and it doesn't even seem like I'm headed in the right direction, so if anyone can give me any tips or what not, I would greatly appreciate it.

singularity
Posting Yak Master

153 Posts

Posted - 2009-06-16 : 19:28:33

[code]
select a.num, a.title, a.email, coalesce(a.phone, b.phone) as phone
from DataTable a
left join
(select email, phone
from
(select email, phone, row_number() over (partition by email order by frequency desc) as rn
from ExtraDataTable)
where rn = 1) b on a.email = b.email [/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-17 : 06:01:03

[code]
small modification to singularity code
select a.num, a.title, a.email, coalesce(a.phone, b.phone) as phone
from DataTable a
left join
(select email, phone, row_number() over (partition by email order by frequency desc) as rn
from ExtraDataTable) b on a.email = b.email
and rn = 1
[/code]

jlyles
Starting Member

2 Posts

Posted - 2009-06-18 : 16:29:03

Thank you two for the quick replies! I did some research on the code, and the coalesce and ranking functions were exactly what I needed and the script works wonderfully. Recently coming from SQL 2000, I had no idea about the Rank and Partition features so it was nice to see the solution was so simple. Thanks again guys!

saran_d28
Starting Member

36 Posts

Posted - 2009-06-19 : 05:30:12

In SQL 2000u can use:

select num, title, email, coalesce(main.phone, extra.phone)
from datatable data
left outer join
(Select * from extradatatable a
join (select email, max(frequency) from extratable) b
on a.mail = b.mail
and a.frequency = b.frequency) extra
on data.email = extra.email

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources