Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

How to create SQL Query without duplicate records for phonetypes?

I have a phonenumber_table with two fields: phonetype and phonenumber. The different phone types are business, fax, etc. When I join this table with my company_table and select phonenumber, I get duplicates because of the different phone types. How can I get both fax number and phone number in the layout (phonenumber as Phone_Number, phonenumber as Fax_Number) whichout duplicate records? My current query is below. Thanks.

select company,phonetype, phonenumber as Phone_Number, phonenumber as Fax_Number, status
from company_table
inner join phonenumber_table on company_table.ID = phonenumber_table.ObjOwner_ID
where status = 'Active'


0
asmyatt
Asked:
asmyatt
1 Solution
 
SQL_SERVER_DBACommented:
use aliases
0
 
BrandonGalderisiCommented:
You need to join to the table for each type.
select c.company, p.phonenumber as Phone_Number, fax.phonenumber as Fax_Number, status
from company_table c
  left outer join phonenumber_table F
   on c.ID = phonenumber_table.ObjOwner_ID
   and f.phoneType = 'FAX'
  left outer join phonenumber_table p
   on c.ID = phonenumber_table.ObjOwner_ID
   and p.phoneType = 'PHONE'
 
where status = 'Active'

Open in new window

0
 
asmyattAuthor Commented:
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now