[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