[prog] SQL Joins

[Rasjid Wilcox]

Tiera,

Did you draw out the tables and relationships between them?

A piece of paper and a pen/pencil is the best way of getting your head around 
the relationships.

We have:

product       contractselection     cus_con          customer
---------     ---------------    ---------------     --------
| id    |--|  | id          |    | customer_id |-----| id   |
| name  |  |  | contract_id |----| contract_id |--|  | name |
| price |  |--| product_id  |    ---------------  |  --------
---------     | price       |                     | 
              ---------------                     |  contract
                                                  |  --------
                                                  |--| id   |
                                                     | name |
                                                     --------

I normally start with a 'select * from tablename ...' first, since then you 
can see what is happening, and cut it down at the end.  Also, as you are 
aware, you need to use left joins in order to get products that don't have a 
special contract discount.

So from the above we have:

select * from product left join contractselection on product.id = product_id
 left join cus_con on contractselection.contract_id = cus_con.contract_id

(We don't need to include the customer and contract tables, unless we want to 
have some more descriptive text in our result.)

Ah!  This gives us the four rows you had before.  But see there is a 
customer_id of 2 in there.  We don't want that.

So we try:

select * from product left join contractselection on product.id = product_id
 left join cus_con on contractselection.contract_id = cus_con.contract_id
 where customer_id = 1

Opps.  Now we only have two rows.  We lost the product without a contract.

So instead we try:

select * from product left join contractselection on product.id = product_id
 left join cus_con on contractselection.contract_id = cus_con.contract_id
 where customer_id = 1 or customer_id is null

This is looking good.

Finially, we have

select product.id, product.name, product.price,
 contractselection.contract_id, contractselection.price as contractprice
 from product left join contractselection on product.id = product_id
 left join cus_con on contractselection.contract_id = cus_con.contract_id
 where customer_id = 1 or customer_id is null

+----+--------+-------+-------------+---------------+
| id | name   | price | contract_id | contractprice |
+----+--------+-------+-------------+---------------+
|  1 | box    |     2 |           1 |             1 |
|  2 | circle |     4 |           1 |             3 |
|  3 | square |     3 |        NULL |          NULL |
+----+--------+-------+-------------+---------------+

So my suggestions are:
- always draw the table relationships out on paper;
- in general do a select * until you have got the final recordset you want, 
and then filter down the fields.

Cheers,

Rasjid.