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

"second newest" record?

I've seen a number of solution to get the "newest record" from a time series
-- and extensively benchmarked them on our 2 million+ row prices database. In
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum id
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
Maury
Maury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:

>I've seen a number of solution to get the "newest record" from a time series
>-- and extensively benchmarked them on our 2 million+ row prices database. In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>
|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.

> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury
|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can't
seem to find it. It might be possible to compare the side (buy/sell) with the
quantity or something, but that seems pretty nasty too.
|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:

>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true:
>they DO sometimes put the correction as the second record, and sometimes the
>third. I've looked through the data for some sort of determinant, but I can't
>seem to find it. It might be possible to compare the side (buy/sell) with the
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

"second newest" record?

I've seen a number of solution to get the "newest record" from a time series
-- and extensively benchmarked them on our 2 million+ row prices database. In
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum id
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
MauryMaury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time series
>-- and extensively benchmarked them on our 2 million+ row prices database. In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can't
seem to find it. It might be possible to compare the side (buy/sell) with the
quantity or something, but that seems pretty nasty too.|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true:
>they DO sometimes put the correction as the second record, and sometimes the
>third. I've looked through the data for some sort of determinant, but I can't
>seem to find it. It might be possible to compare the side (buy/sell) with the
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

"second newest" record?

I've seen a number of solution to get the "newest record" from a time series
-- and extensively benchmarked them on our 2 million+ row prices database. I
n
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum i
d
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grr
r.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
MauryMaury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:

>I've seen a number of solution to get the "newest record" from a time serie
s
>-- and extensively benchmarked them on our 2 million+ row prices database.
In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum
id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Gr
rr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.

> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can'
t
seem to find it. It might be possible to compare the side (buy/sell) with th
e
quantity or something, but that seems pretty nasty too.|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:

>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true
:
>they DO sometimes put the correction as the second record, and sometimes th
e
>third. I've looked through the data for some sort of determinant, but I can
't
>seem to find it. It might be possible to compare the side (buy/sell) with t
he
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)