Project 5-3 Combine Table Data with JOIN

In this project, you will work through a few examples of JOIN functions using the DuckWear tables.

Step by Step

Use the following command to create a report that shows the correct customer title from the duck_title table, and the customer's last name and state from the duck_cust table. Sort the data by the state field.
```
 SELECT duck_title.title_cust, duck_cust.cust_last,  duck_cust.cust_state FROM duck_title, duck_cust WHERE duck_title.title_num=duck_cust.cust_title ORDER BY duck_cust.cust_state; 
```
This command is syntactically correct but cumbersome and overly specific.

Note
Unless a command uses fields from tables that have the same name, the table_name.field_name syntax is not necessary and can lead to errors due to typos.
Create the same report using the JOIN function in the following command.
```
 SELECT title_cust, cust_last, cust_state FROM duck_title RIGHT JOIN duck_cust ON title_num=cust_title ORDER BY cust_state; 
```
This produces a report that is identical to the one you created in step 1.
As a sales promotion, DuckWear is going to give away one each of the nine outfits in the duck_sales table to the corresponding oldest account numbers . Using the following command, create a report that lists the customer number, last name, duck s name, and the outfit that will be their prize.
```
 SELECT cust_num, cust_last, cust_duckname, design_name FROM duck_cust RIGHT JOIN duck_sales ON cust_num=design_num; 
```

Create a report listing the outfit won, then the address to send it to using the duck_sales and duck_cust tables.

 SELECT design_name, cust_add1, cust_add2, cust_city, cust_state,   cust_zip1, cust_zip2 FROM duck_sales LEFT JOIN duck_cust ON cust_num=design_num;

Project Summary

The choice between using a LEFT JOIN or a RIGHT JOIN usually revolves around the comparison made in the ON clause and whether the tables involved have a different number of rows. If the table with the least rows is on the left of the FROM clause, a LEFT JOIN will result in a report that has only that many rows. If a RIGHT JOIN is used in those circumstances, the resulting report will have some of the SELECT fields padded with NULL, as there will be no corresponding entries in the shorter table. If the tables have the same amount of rows and the comparison fields are all full, you can use an INNER JOIN, although most of the time, a LEFT or RIGHT JOIN will return the same result.