SQL Select data from two tables (one row -> multiple rows)

Suppose I have two SQL tables: Customers and PhoneNumbers.

Suppose that Customers has the following columns: cust开发者_运维百科omerId (primary key), fName, lName.

Suppose that PhoneNumbers has the following columns: phoneNumberId (primary key), phoneNumber, customerId (foreign key).

What I understand so far is that if each customer has one phone number, I can select the fName, lName, and phoneNumber of each customer with the following SQL:

SELECT 
    customer.fName, customer.lName, phone.phoneNumber 
FROM 
    Customers customer 
        INNER JOIN phoneNumbers phone ON 
            customer.customerId = phone.customerId

What if a customer may have more than one phone number? How do I get a list of customers with the list of phone numbers of each customer?

My programming language to drive the SQL is C#/.NET.

As you say, if there is exactly one PhoneNumber per customer, your query will work.

Customers with more than one Phone Number will also be returned, but the customer record will be duplicated for each different phone number.

The other condition you need to consider is customers with no phone numbers. If you INNER JOIN the tables, then these customers will be excluded from the result set. To include such customers, you need to use an OUTER JOIN.

The query you've given will return multiple phone numbers if they exist. You'll see rows with customer.fName and customer.lName repeated, each with a different phone.phoneNumber.

You can do the exact same thing. The query you currently list should retrieve the exact results you want; one row per phone number per customer.

You'll get several rows for one customer if it has several numbers.