SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to get desired result using simple sql query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akash.agrawalin
Starting Member

4 Posts

Posted - 11/01/2012 :  12:41:26  Show Profile  Reply with Quote
I have 2 tables as below

Table1: Person

Id FNAME LNAME
1 xxx yyy
2 aaa zzz

Table2: Contacts

ContactID Id PHONENUMBER PHONETYPE
1 1 12345 HOME
2 1 67890 OFFICE
3 2 54321 HOME
4 2 09876 OFFICE

I want the result for my report as below:

ID FNAME LNAME HOME OFFICE
1 xxx yyy 12345 67890
2 aaa zzz 54321 09876

Can you please help me write the query to get this kind of result? It will be of great help.....

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1721 Posts

Posted - 11/01/2012 :  13:15:38  Show Profile  Reply with Quote
select p.id, p.fname, p.lname, h.phonenumber, o.phonenumber
from Person p
inner join
  Contacts h
    on  p.id = h.id
    and h.phonetype = 'HOME'
inner join
  Contacts o
    on  p.id = o.id
    and o.phonetype = 'OFFICE'
You may want to use outer joins to support the case where a Person does not have both types of phones. Also, a PIVOT might be a better approach, but I rarely use it so can't really speak to that.

HTH

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry
Go to Top of Page

akash.agrawalin
Starting Member

4 Posts

Posted - 11/01/2012 :  13:35:32  Show Profile  Reply with Quote
Bustaz,

Thanks for your reply. But this query is giving me empty result set.
Go to Top of Page

akash.agrawalin
Starting Member

4 Posts

Posted - 11/01/2012 :  13:58:21  Show Profile  Reply with Quote
hey,sorry for that post. Its working fine. Thanks a lot....
Go to Top of Page

akash.agrawalin
Starting Member

4 Posts

Posted - 11/01/2012 :  14:05:54  Show Profile  Reply with Quote
I have 1 more problem regarding this. My table2 rows might grow in future to have more phone numbers like mobile1,mobile2. So i need some dynamic sql to solve this issue.Can you please also let me know how can i achieve this?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000