[prog] SQL Joins

[tiera]

On Wed, Apr 14, 2004 at 08:48:09AM -0700, Cynthia Kiser wrote:
> 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))
> 
Hi!

I want all the products. And if a product is connected to the specific
contract.id I'm interested in I want that contract information too.
But the problem is that a product can be connected to more than one
contract and with my query I would get one extra product row for every
product-contract connection. So I guess what I was looking for was a
way to make a join with just the contract rows that have a specific
contract.id. I couldn't find a solution so I ended up getting all the
products first and then for every product checking if there's some
contract information. 

Thanks.

/Tiera