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 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).
