Finding all the rows with 2 conditions using SQL (separate tables)

Question

I'm having some problems with plain old SQL queries (drawback of using ORMs most of the time :)).

I'm having 2 tables, PRODUCTS and RULES. In table RULES I have defined rules for products. What I want is to write a query to get all products which have defined rules.

Rules are defined by 2 ways:

1. You can specify RULE for only one product (ProductID have value, SectorID is NULL)
2. You can specify RULE for more that one product using SectorID (ProductID is NULL)

Result need to have all products which have rule (product.ID - rule.ProductID) but also all products that are defined in sectors which are in rules table (product.SectorID - rule.SectorID).

Also, the result can't have duplicate products (products which are defined by productId in RULES or by SectorID)

Example:

PRODUCTS

ID  SectorID
1   1
2   1
3   1
4   2
5   3
6   3

RULES

ID ProductID SectorID
1  1         NULL
4  NULL      1
5  6         NULL

Expected result

PRODUCTS with IDs : 1, 2, 3, 6

Answer 1

Simplest way I can think of, but not necessarily the quickest.

SELECT * FROM products AS p WHERE
       EXISTS (SELECT * FROM rules AS r WHERE p.ID = r.ProductID OR p.SectorID = r.SectorID)