TSQL Query problem

I've been stuck on a query where there is a CUSTOMER and a PHONE table.

It is a one to many relationship, in which a CUSTOMER and have many开发者_如何学Python PHONE numbers

The table is like the following:

---CUSTOMER---
cust_ID
cust_Name

---PHONE---
cust_ID
Phone_type (M or O for mobile/office respectively)
phone_no

I want to write a query where the output would be similar to the following:

cust_name | M_Phone | O_Phone

Where the M_Phone is the mobile number and the O_Phone is the office number.

select C.cust_Name,
       PM.phone_no as M_Phone,
       PO.phone_no as O_Phone
from CUSTOMER as C
  left outer join @PHONE as PM
    on C.cust_ID = PM.cust_ID and
       PM.phone_no = 'M'
  left outer join PHONE as PO
    on C.cust_ID = PO.cust_ID and
       PO.phone_no = 'O'

The above query will give you customers without any phone numbers as well. If you only want customer that actually have a phone add this where clause.

where PM.phone_no is not null or
      PO.phone_no is not null 

In some DBMS, this would work:

SELECT M.Cust_ID, M.Phone_No AS M_Phone, O.Phone_No AS O_Phone
  FROM (SELECT Cust_ID, Phone_No FROM Phone WHERE Phone_Type = 'M') AS M
  FULL OUTER JOIN
       (SELECT Cust_ID, Phone_No FROM Phone WHERE Phone_Type = 'O') AS O
    ON M.Cust_ID = O.Cust_ID;

The FULL OUTER JOIN will select customers with both a mobile and an office phone, or with just one but not the other. If you also want to list customers with neither, you need a slightly different query (and will need to reference the Customer table).

Note that this does not select from the Customer table; I assume that there are no customer ID values in the Phone table that do not also exist in the Customer table (so you have correct referential integrity constraints in place).