How to Select Multiple Records from Multiple Tables at Once

Question

I have two tables Customer and CustomerPhone. Customer usually has multiple phone numbers, so when i run select statement on customer 101, i will get multiple records due to the multiple phone numbers.

As you see below all the "Phone" and "Fax" field belongs to CustomerPhone table. These are considered as two records in the CustomerPhone table whereas the rest of fields relate to Customer table which is a single record.

What should i do to fill Phone and Fax field in this case? Should i run select statement on CustomerPhone first and then run select statement on Customer?

Answer 1

I'm guessing that your CustomerPhone table looks something like

CustomerPhone
CustomerID int
Number varchar
PhoneType   phone | fax

It seems the UI allows for just one regular phone and fax number. If that's the case, and customer only has at most one phone, one fax (but may have none) i.e. a unique index on CustomerID/PhoneType in CustomerPhone, then you can retrieve all the information as one query:

SELECT c.*, phone.Number, fax.Number FROM Customer c 
LEFT JOIN CustomerPhone phone ON phone.CustomerID=c.CustomerID
LEFT JOIN CustomerPhone fax = fax.CustomerID=c.CustomerID