How to Select Multiple Records from Multiple Tables at Once

I hav开发者_如何转开发e two tables, Customer and CustomerPhone. Customer usually has multiple phone numbers, so when I run a select statement on customer 101, I will get multiple records due to the multiple phone numbers.

All the "Phone" and "Fax" field belongs to the 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 the Phone and Fax field in this case? Should I run a select statement on CustomerPhone first and then run a select statement on Customer?

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