[prog] SQL Joins

[Rasjid Wilcox]

On Saturday 17 April 2004 15:52, Rasjid Wilcox wrote:
> 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

Damn.  I just realised a fatal flaw in the above.

It does not work for customer_id = 2.

This works better:

select product.id, product.name, product.price,  
contractselection.contract_id, contractselection.price as contractprice
from (cus_con inner join contractselection on cus_con.contract_id = 
contractselection.contract_id and customer_id = 2)
 right join product on contractselection.product_id = product.id where 
customer_id = 2

I am fairly sure this will work under all cases, but it is after midnight and 
I'm a bit tired - so there still could be a flaw.

Working out why my first suggestion failed for customer.id = 2, and if this 
one always works is left as an exercise for the reader.  ;-)

> 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.

Additionally:
- check your query with various kinds of data, particularly edge cases.
- make sure you have had enough sleep  :-)

Cheers,

Rasjid.