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,

Chris

Hello, 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

Show quoteHide quote

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);

Hierarchically arranged attributes...mission impossible?
problem: select Max(date) which is less than actual Max(date) ?
Parameterize table name without constructing dynamic query?
use trigger
Simple: Assign a value to a Outout variable from a select statemen