"Selective" OR join

Hi- I am trying to join 2 tables in such a manner:
select i.itemcode, i.color, p.price from items i join prices p on i.itemcode
= p.itemcode AND
(i.color = p.color or p.color is null)
BUT- I only want the results to contain one case in the or condition, OR the
other, preferably the first. If the first case doesn't exist (i.color =
p.color) then, we choose the second case, p.color is null. But if the first
case does exist, we get that case.
For example
item table
itemcode color
1 red
1 blue
2 yellow
price table
itemcode color price
1 red 5
1 orange 7
1 null 10
2 yellow 15
would yield
1 red 5
1 blue 10
2 yellow 15
the 1 red 10 row would not be generated!
Any ideas on how to pull this off? I've tried all sorts of cases and ifs,
but I can't seem to manage it.
Thanks,
ChrisHello, Chris
You should post DDL as "CREATE TABLE" statements (including all
constraints) and sample data as "INSERT INTO ... VALUES (...)"
statements. See: http://www.aspfaq.com/etiquette.asp?id=5006
I have considered the following DDL and sample data:
CREATE TABLE item (
itemcode int,
color varchar(10),
PRIMARY KEY (itemcode, color)
)
CREATE TABLE price (
itemcode int NOT NULL,
color varchar(10) NULL,
price numeric(10,2) NOT NULL,
UNIQUE (itemcode, color)
)
INSERT INTO item VALUES (1,'red')
INSERT INTO item VALUES (1,'blue')
INSERT INTO item VALUES (2,'yellow')
INSERT INTO item VALUES (3,'green')
INSERT INTO item VALUES (3,'purple')
INSERT INTO price VALUES (1,'red',5)
INSERT INTO price VALUES (1,'orange',7)
INSERT INTO price VALUES (1,null,10)
INSERT INTO price VALUES (2,'yellow',15)
INSERT INTO price VALUES (3,null,25)
Note the unique constraint in the price table, which says that there
can only be one price for each color (i.e. the "null" color can appear
only one time for each itemcode). The sample data is expanded a bit, to
show another posible case.
You can use one of the following queries:
SELECT i.itemcode, i.color, p1.price
FROM item i INNER JOIN price p1 ON p1.itemcode=i.itemcode
AND (p1.color=i.color
OR p1.color IS NULL AND NOT EXISTS (
SELECT * FROM price p2
WHERE p2.itemcode=i.itemcode AND p2.color=i.color
)
)
SELECT i.itemcode, i.color, COALESCE(p1.price,p2.price) as price
FROM item i
LEFT JOIN price p1 ON p1.itemcode=i.itemcode AND p1.color=i.color
LEFT JOIN price p2 ON p2.itemcode=i.itemcode AND p2.color IS NULL
Test to see which one has a better performance with your real data.
With this sample data, they seem equally good (although they have
different execution plans).
Razvan|||A little comment:
The queries may produce different results if there are items that have
no prices. For example, add the following to the sample data:
INSERT INTO item VALUES (4,'pink')
The first query will not return any row for this item, but the second
query will return a row with a NULL price. If you want this row to be
excluded from the second query, you can add this line (at the end of
the second query):
WHERE p1.price IS NOT NULL OR p2.price IS NOT NULL
Razvan|||Thanks much Razvan, I will try this and follow up...
Chris
"Razvan Socol" wrote:

> A little comment:
> The queries may produce different results if there are items that have
> no prices. For example, add the following to the sample data:
> INSERT INTO item VALUES (4,'pink')
> The first query will not return any row for this item, but the second
> query will return a row with a NULL price. If you want this row to be
> excluded from the second query, you can add this line (at the end of
> the second query):
> WHERE p1.price IS NOT NULL OR p2.price IS NOT NULL
> Razvan
>|||Is this what you wanted?
SELECT I.item_code, I.color_nbr, P.price_amt
FROM items AS I, Prices AS P
WHERE I.item_code = P.item_code
AND I.color_nbr = COALESCE (P.color_nbr, I.color_nbr);