[prog] SQL Joins

[tiera]

On Sun, Apr 18, 2004 at 12:39:12AM +1000, Rasjid Wilcox wrote:
> 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

Hi Rasjid!

Thanks!! Very good step by step explanation of how you're thinking. It
never occured to me to use the customer_id. I will have to go through
it a couple of times to make sure I really understand it. 

> 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  :-)
> 

Sleep is always good. =)
Good suggestions, I will apply them the next time I run into a problem
like this.

> Cheers,
> 
> Rasjid.
>

/Tiera