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

Question

Suppose I have two SQL tables: Customers and PhoneNumbers.

Suppose that Customers has the following columns: customerId (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.

@casperOne, thanks for formatting my code :-)

Vivian River
– Vivian River

2010-12-09 14:54:01 +00:00
Commented Dec 9, 2010 at 14:54 — Vivian River
– Vivian River, Commented Dec 9, 2010 at 14:54
logiclabz.com/sql-server/…

The King
– The King

2010-12-09 14:54:04 +00:00
Commented Dec 9, 2010 at 14:54 — The King
– The King, Commented Dec 9, 2010 at 14:54

StuartLC · Accepted Answer · 2010-12-09 14:56:19Z

4

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.

answered Dec 9, 2010 at 14:56

StuartLC

108k18 gold badges225 silver badges295 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Stefanelli · Accepted Answer · 2010-12-09 14:55:47Z

2

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.

answered Dec 9, 2010 at 14:55

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Comments

GendoIkari · Accepted Answer · 2010-12-09 14:54:56Z

0

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.

answered Dec 9, 2010 at 14:54

GendoIkari

11.9k6 gold badges68 silver badges108 bronze badges

Comments

Denis Palnitsky · Accepted Answer · 2010-12-09 14:55:03Z

0

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

answered Dec 9, 2010 at 14:55

Denis Palnitsky

18.4k14 gold badges49 silver badges57 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related