[prog] SQL Joins

tiera tiera at comhem.se
Wed Apr 14 10:42:26 EST 2004


Hi!

I'm in desperate need of some sql-help. 

I have 5 tables, customer,product,contract,cus_con and
contractselection. They look like this with some fake data added.

customer
--------
id	name
1	John
2	Sam

product
-------
id	name	price
1	box	2
2	circle	4
3	square	3

contract
--------
id	name
1	John's special
2	Sam's special

cus_con
-------
customer_id	contract_id
1		1
1		2

contractselection
-----------------
id	contract_id	product_id	price
1	1		1		1
2	1		2		3
3	2		1		NULL/1.2 (just to show that
the price here can be NULL or have a value)

What I want to do is for a choosen customer get every product in the
product table and if the customer is connected to a contract get the
contract.id and the contractselection.price for the customers
contract. All this at the same time. So I would like to get this (for
customer.id=1 and contract.id=1):

product.id product.name product.price contract.id contractselection.price
1          box          2             1           1
2          circle       4             1           3
3          square       3             NULL        NULL

The sql-query that I thought would work looks like this:
SELECT product.id,product.name,product.price,
       contract.id,contractselection.price
FROM product LEFT JOIN contractselection ON 
     product.id=contractselection.product_id 
     LEFT JOIN contract ON
     contractselection.contract_id=contract.id AND contract.id=1

But that query gives me:
product.id product.name product.price contract.id contractselection.price
1          box          2             1           1
*1	   box		2	      NULL        NULL/1.2
2          circle       4             1           3
3          square       3             NULL        NULL

The row marked with a * is the one that I don't want to get! I now
understand why I get the extra row also, but I can't come up with a
solution that will give me just the rows I wan't. I thought the "AND
contract.id=1" would do that, but no.

I hope I have explained it so someone can understand. I don't know
what to do... 

Appreciate any ideas...

/Tiera





More information about the Programming mailing list