[prog] SQL Joins
tiera at comhem.se
Mon Apr 19 09:50:15 EST 2004
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
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.
> - 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
More information about the Programming