CODEWITHSUNDEEP
mysqlsql
ZeusofCode
ZeusofCode

Reputation: 83

SQL Query to Return only one row per customer

The database tables I am working with are as the following:

Type_Telephone

ID_Type_Telephone (PK) Description
0 LandLine
1 Cellular
2 Telecopier

Telephone

ID_Telephone (PK) ID_Client (FK) ID_Type_Telephone (FK) Numero
100 201 0 514-555-0165
101 201 1 514-555-0155
102 202 1 514-555-0176
103 200 0 514-555-0164
104 200 1 514-555-0119

Client

ID_Client (PK) Nom Prenom
200 Bertrand Antoine
201 Legault Claude
202 Leonard Sylvie

I would like to write a SQL query that lists all customers' Cellular and Landline telephones. I would like to have only one row of results per customer such as:

Nom Prenom Landline Cellular
Bertrand Antoine 514-555-0164 514-555-0119
Legault Claude 514-555-0165 514-555-0155
Leonard Sylvie 514-555-0176

Any advice how to generate this ? Thank you !

Upvotes: 0

Views: 809

Answers (2)

Akina
Akina

Reputation: 42834

SELECT c.nom,
       c.prenom,
       t0.numero landline,
       t1.numero cellular
FROM Client c
LEFT JOIN Telephone t0 ON c.id = t0.ID_Client AND t0.ID_Type_Telephone = 0
LEFT JOIN Telephone t1 ON c.id = t1.ID_Client AND t1.ID_Type_Telephone = 1

If you need one more column for Telecopier then add one more table copy.

The query assumes that Telephone (ID_Client, ID_Type_Telephone) is defined as UNIQUE. If not then

SELECT c.nom,
       c.prenom,
       GROUP_CONCAT(t0.numero) landline,
       GROUP_CONCAT(t1.numero) cellular
FROM Client c
LEFT JOIN Telephone t0 ON c.id = t0.ID_Client AND t0.ID_Type_Telephone = 0
LEFT JOIN Telephone t1 ON c.id = t1.ID_Client AND t1.ID_Type_Telephone = 1
GROUP BY 1, 2

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

This requires a pivot query, something like this:

SELECT
    c.ID_Client,
    c.Nom,
    c.Prenom,
    MAX(CASE WHEN tt.Description = 'LandLine' THEN t.Numero END) AS Landline,
    MAX(CASE WHEN tt.Description = 'Cellular' THEN t.Numero END) AS Cellular
FROM Client c
LEFT JOIN Telephone t
    ON t.ID_Client = c.ID_Client
LEFT JOIN Type_Telephone tt
    ON tt.ID_Type_Telephone = t.ID_Type_Telephone
GROUP BY
    c.ID_Client,
    c.Nom,
    c.Prenom;

If you also want to include possible telecopier numbers, then add another max of CASE expression to the above query.

Upvotes: 2

Related Questions