[prog] SQL Joins

[Cynthia Kiser]

Quoting tiera <tiera at comhem.se>:
> 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.

So the problem is that you need to do outer joins because some
products aren't covered by contracts. But you don't want those rows if
the product is covered by a contract. I don't immediately see a way to
do that in one query. How about doing a union of the "on contract"
query (basically what you have but with inner joins) and and "off
contract" query. I don't speak the my-sql dialect of SQL but something
approximating:
	select **info you want** 
	from *product_tables* 
	where product_id not in (subquery for on contract stuff))

-- 
Cynthia N. Kiser
cnk at ugcs.caltech.edu