Its time for the annual trade show and you want to make sure all your suppliers make it to the party. The marketing team needs a list of all your suppliers, their full delivery address, and the name of both the primary and alternate contacts for the supplier. Order the results by supplier name.

You'll be using a number of tables - Suppliers from the Purchasing schema and the People, Cities, and StateProvinces tables in the Application schema. The columns to join things up are a bit more tricky - - Suppliers to people on the PersonID and the PrimaryContactPersonID or AlternateContactPersonID. - Cities on CityID to Suppliers on DeliveryCityID - StateProvices on StateProvinceID (on both sides)

Something to keep in mind... you can join to the same table multiple times as long as you give them different aliases.

This query will return 13 rows and 9 columns. An example to compare to -

SupplierName PrimaryContactName AlternateContactName DeliveryAddressLine1 DeliveryAddressLine2 DeliveryCityID CityName StateProvinceCode DeliveryPostalCode A Datum Corporation Reio Kabin Oliver Kivi Suite 10 183838 Southwest Boulevard 38171 Zionsville IN 46077

*/