/
CustomersByPhoneType.sql
31 lines (29 loc) · 1013 Bytes
/
CustomersByPhoneType.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
DECLARE @PhoneTypeIdentifier INT= 3;
/*
Get all customers with only work phone
*/
SELECT Cust.CustomerIdentifier,
Cust.ContactId,
Cust.CountryIdentifier,
Cust.ContactTypeIdentifier,
CD.PhoneTypeIdentifier,
Cust.CompanyName,
Cust.Street,
Cust.City,
Cust.Region,
Cust.PostalCode,
Cust.Phone,
Cust.Fax,
Cust.ModifiedDate,
Countries.[Name] AS country,
C.FirstName,
C.LastName,
CT.ContactTitle,
CD.PhoneNumber
FROM Customers AS Cust
INNER JOIN Contacts AS C ON Cust.ContactId = C.ContactId
INNER JOIN Countries ON Cust.CountryIdentifier = Countries.CountryIdentifier
INNER JOIN ContactType AS CT ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
AND C.ContactTypeIdentifier = CT.ContactTypeIdentifier
INNER JOIN ContactDevices AS CD ON C.ContactId = CD.ContactId
WHERE CD.PhoneTypeIdentifier = @PhoneTypeIdentifier;