I need to read through two tables, first stepping thru the first one,
finding a value to a particular field (model no) along with the value of
another (price), and then read thru the second table, looking for the same
field value (model no), and if found, use that (price) field vale verses the
original value for the (price) field.
TABLE 1
Model No. Price
X45 $30
X60 $50
TABLE 2
Model No. Price
X50 $35
X60 $48
RESULT SET
Model No. Price
X45 $30
X50 $35
X60 $48
Thanks,
DonOne method:
SELECT
COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
COALESCE(t2.Price, t1.Price) AS Price
FROM Table1 t1
FULL JOIN Table2 t2 ON
t1.ModelNo = t2.ModelNo
ORDER BY 1
Hope this helps.
Dan Guzman
SQL Server MVP
<dbj> wrote in message news:%23D%23EntISFHA.2132@.TK2MSFTNGP14.phx.gbl...
>I need to read through two tables, first stepping thru the first one,
>finding a value to a particular field (model no) along with the value of
>another (price), and then read thru the second table, looking for the same
>field value (model no), and if found, use that (price) field vale verses
>the original value for the (price) field.
> TABLE 1
> Model No. Price
> X45 $30
> X60 $50
> TABLE 2
> Model No. Price
> X50 $35
> X60 $48
> RESULT SET
> Model No. Price
> X45 $30
> X50 $35
> X60 $48
> Thanks,
> Don
>
>|||Hi Dan,
Thanks for the help. It appears as though this is very close. But, I am
still getting duplicate records for the same model number, when the desire
is to only use the record from table 2 when the same model no is found in
both.
Here is the result set I am getting:
Model No. Price
X45 $30
X50 $35
X60 $50
X60 $48
When I am looking for:
Model No. Price
X45 $30
X50 $35
X60 $48 <-- Table 2 is specifically used when same
model no is in both tables.
Below is the actual code being used:
SELECT TOP 100 PERCENT COALESCE (dbo.hb_view_organization_plan.plan_id,
dbo.hb_view_business_unit_plan.plan_id,
dbo.hb_view_subdivision_plan.plan_id,
dbo.hb_view_lot_plan.plan_id) AS plan_id, COALESCE
(dbo.hb_view_organization_plan.plan_no_name,
dbo.hb_view_business_unit_plan.plan_no_name,
dbo.hb_view_subdivision_plan.plan_no_name,
dbo.hb_view_lot_plan.plan_no_name)
AS plan_no_name, COALESCE
(dbo.hb_view_organization_plan.current_retail_price,
dbo.hb_view_business_unit_plan.current_retail_price,
dbo.hb_view_subdivision_plan.current_retail_price,
dbo.hb_view_lot_plan.current_retail_price) AS current_retail_price,
COALESCE (dbo.hb_view_organization_plan.source,
dbo.hb_view_business_unit_plan.source, dbo.hb_view_subdivision_plan.source,
dbo.hb_view_lot_plan.source) AS source
FROM dbo.hb_view_organization_plan FULL OUTER JOIN
dbo.hb_view_business_unit_plan ON
dbo.hb_view_organization_plan.plan_id =
dbo.hb_view_business_unit_plan.plan_id FULL OUTER JOIN
dbo.hb_view_lot_plan FULL OUTER JOIN
dbo.hb_view_subdivision_plan ON
dbo.hb_view_lot_plan.plan_id = dbo.hb_view_subdivision_plan.plan_id ON
dbo.hb_view_business_unit_plan.plan_id =
dbo.hb_view_subdivision_plan.plan_id
ORDER BY dbo.hb_view_organization_plan.plan_no_name
The logic here is that is to sum all the records and when a duplicate
plan_no_name is found, is to display only one record from the lowest in the
hierarchy, which organization, business_unit, subdivsion, and lot. Always
use the lot record first (lowest), subdivision second, business unit third,
and finally organization last (highest).
Again, thanks for your help.
Regards,
Don
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O1LO80ISFHA.2788@.TK2MSFTNGP09.phx.gbl...
> One method:
> SELECT
> COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
> COALESCE(t2.Price, t1.Price) AS Price
> FROM Table1 t1
> FULL JOIN Table2 t2 ON
> t1.ModelNo = t2.ModelNo
> ORDER BY 1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <dbj> wrote in message news:%23D%23EntISFHA.2132@.TK2MSFTNGP14.phx.gbl...
>|||You design is wrong. Given a fixed hierarchy, each level should be in
the same row since it is an attribute of that entity. This is a basic
principle of data modeling. You might want to read what Chris Date and
Dave McGoveran said about this kind of design error. They called it
orthogonal design, while I prefer attribute splitting.
duplicate plan_no_name is found, is to display only one record [sic]
from the lowest in the hierarchy, which organization, business_unit,
subdivsion, and lot. Always use the lot record [sic] first (lowest),
subdivision second, business unit third, and finally organization last
(highest). <<
Row are not records and until you learn the differences, you will not
be able to write good SQL. You will think in terms of DML for
solutions that shoudl have been done with correct DDL. Since you did
not post DDL, it is impossible to make anything but a guess about the
data.
And using things like "view" or "tbl" in a data element name to tell us
about the storage method is a violationfo ISO-11179 and good data
mdoeling practices, too.|||On Sun, 24 Apr 2005 15:17:14 -0500, <dbj> wrote:
>Hi Dan,
>Thanks for the help. It appears as though this is very close. But, I am
>still getting duplicate records for the same model number, when the desire
>is to only use the record from table 2 when the same model no is found in
>both.
>Here is the result set I am getting:
>Model No. Price
> X45 $30
> X50 $35
> X60 $50
> X60 $48
>When I am looking for:
>Model No. Price
> X45 $30
> X50 $35
> X60 $48 <-- Table 2 is specifically used when same
>model no is in both tables.
>Below is the actual code being used:
(snip)
Hi Don,
Checking the code you posted, I see that you're not attempting to combine
two tables, but a total of four! Now, whereas a full outer join betwee two
tables is not very tough, a full outer join of three or more tables has
some gotchas in the ON clause.
Here's a simplified version of a four-way full outer join to show you how
you should approach this:
SELECT COALESCE (t1.keycol, t2.keycol, t3.keycol, t4.keycol),
COALESCE (t1.datacol, t2.datacol, t3.datacol, t4.datacol)
FROM Table1 AS t1
FULL JOIN Table2 AS t2
ON t2.keycol = t1.keycol
FULL JOIN Table3 AS t3
ON t3.keycol = COALESCE(t1.keycol, t2.keycol) -- Gotcha!
FULL JOIN Table4 AS t4
ON t4.keycol = COALESCE(t1.keycol, t2.keycol, t3.keycol) -- Gotcha!
I'll leave it too you to fill this pattern with your (IMO much too long)
table and column names.
Oh, and by the way - TOP 100 PERCENT is totally meaningless; it clutters
your query, and in the worst case introduces unnecessary overhead at
execution time. Please remove it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||<dbj> wrote in message news:OJ3NcqQSFHA.3144@.tk2msftngp13.phx.gbl...
> Hi Dan,
> Thanks for the help. It appears as though this is very close. But, I am
> still getting duplicate records for the same model number, when the desire
> is to only use the record from table 2 when the same model no is found in
> both.
> Here is the result set I am getting:
> Model No. Price
> X45 $30
> X50 $35
> X60 $50
> X60 $48
> When I am looking for:
> Model No. Price
> X45 $30
> X50 $35
> X60 $48 <-- Table 2 is specifically used when same
> model no is in both tables.
I get the expected results using the sample data provided in your original
post. Below is the complete script:
CREATE TABLE Table1
(
ModelNo char(3) NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
price int NOT NULL
)
CREATE TABLE Table2
(
ModelNo char(3) NOT NULL
CONSTRAINT PK_Table2 PRIMARY KEY,
price int NOT NULL
)
INSERT INTO Table1 VALUES('X45', 30)
INSERT INTO Table1 VALUES('X60', 50)
INSERT INTO Table2 VALUES('X50', 35)
INSERT INTO Table2 VALUES('X60', 48)
SELECT
COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
COALESCE(t2.Price, t1.Price) AS Price
FROM Table1 t1
FULL JOIN Table2 t2 ON
t1.ModelNo = t2.ModelNo
ORDER BY 1
--results
ModelNo Price
-- --
X45 30
X50 35
X60 48
> Below is the actual code being used:
> SELECT TOP 100 PERCENT COALESCE
> (dbo.hb_view_organization_plan.plan_id,
> dbo.hb_view_business_unit_plan.plan_id,
> dbo.hb_view_subdivision_plan.plan_id,
> dbo.hb_view_lot_plan.plan_id) AS plan_id, COALESCE
> (dbo.hb_view_organization_plan.plan_no_name,
> dbo.hb_view_business_unit_plan.plan_no_name,
> dbo.hb_view_subdivision_plan.plan_no_name,
> dbo.hb_view_lot_plan.plan_no_name)
> AS plan_no_name, COALESCE
> (dbo.hb_view_organization_plan.current_retail_price,
> dbo.hb_view_business_unit_plan.current_retail_price,
> dbo.hb_view_subdivision_plan.current_retail_price,
> dbo.hb_view_lot_plan.current_retail_price) AS current_retail_price,
> COALESCE (dbo.hb_view_organization_plan.source,
> dbo.hb_view_business_unit_plan.source,
> dbo.hb_view_subdivision_plan.source,
> dbo.hb_view_lot_plan.source) AS source
> FROM dbo.hb_view_organization_plan FULL OUTER JOIN
> dbo.hb_view_business_unit_plan ON
> dbo.hb_view_organization_plan.plan_id =
> dbo.hb_view_business_unit_plan.plan_id FULL OUTER JOIN
> dbo.hb_view_lot_plan FULL OUTER JOIN
> dbo.hb_view_subdivision_plan ON
> dbo.hb_view_lot_plan.plan_id = dbo.hb_view_subdivision_plan.plan_id ON
> dbo.hb_view_business_unit_plan.plan_id =
> dbo.hb_view_subdivision_plan.plan_id
> ORDER BY dbo.hb_view_organization_plan.plan_no_name
> The logic here is that is to sum all the records and when a duplicate
> plan_no_name is found, is to display only one record from the lowest in
> the hierarchy, which organization, business_unit, subdivsion, and lot.
> Always use the lot record first (lowest), subdivision second, business
> unit third, and finally organization last (highest).
> Again, thanks for your help.
> Regards,
> Don
>
I wouldn't expect duplicate plan_ids as long as that is the is the primary
key of these tables. However, you might very well have duplicate
plan_no_name data. In that case, it is a symptom of a flaw in your data
model.
Hope this helps.
Dan Guzman
SQL Server MVP|||Call me old-fashioned or just plain dumb, but I'd go about it like
this:
SELECT t1.modelno, price = isnull(t2.price,t1.price)
FROM t1
LEFT JOIN t2 ON t1.modelno = t2.modelno
The existence of a row in t2 results in the t2 price; whereas an
absence of a row in t2 results in t1 price.|||> The existence of a row in t2 results in the t2 price; whereas an
> absence of a row in t2 results in t1 price.
>
True, but also the absence of a row in t1 should provide the t2 price based
on Don's requirements, hence the FULL JOIN. With his original data:
SELECT t1.modelno, price = isnull(t2.price,t1.price)
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.modelno = t2.modelno
Results:
modelno price
-- --
X45 30
X60 48
Desired results:
modelno price
-- --
X45 30
X50 35
X60 48
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeme" <jeme.rey@.gmail.com> wrote in message
news:1114383919.282023.11520@.o13g2000cwo.googlegroups.com...
> Call me old-fashioned or just plain dumb, but I'd go about it like
> this:
> SELECT t1.modelno, price = isnull(t2.price,t1.price)
> FROM t1
> LEFT JOIN t2 ON t1.modelno = t2.modelno
> The existence of a row in t2 results in the t2 price; whereas an
> absence of a row in t2 results in t1 price.
>|||Hi Hugo,
Your suggestion worked. Thanks VERY MUCH for your help. And I would also
like to thank everyone else who responded as well. I am sure my
inexperience was apparent but really appreciate your patience helping me
through this. Below is your code that worked!
SELECT TOP 100 PERCENT COALESCE (t1.plan_id, t2.plan_id, t3.plan_id,
t4.plan_id) AS plan_id, COALESCE (t1.plan_availability_pricing_id,
t2.plan_availability_pricing_id,
t3.plan_availability_pricing_id, t4.plan_availability_pricing_id) AS
plan_availability_pricing_id,
COALESCE (t1.plan_number, t2.plan_number,
t3.plan_number, t4.plan_number) AS plan_number, COALESCE (t1.plan_number,
t2.plan_name,
t3.plan_name, t4.plan_name) AS plan_namer, COALESCE
(t1.plan_no_name, t2.plan_no_name, t3.plan_no_name, t4.plan_no_name) AS
plan_no_name,
COALESCE (t1.current_retail_price,
t2.current_retail_price, t3.current_retail_price, t4.current_retail_price)
AS current_retail_price, COALESCE (t1.source,
t2.source, t3.source, t4.source) AS source
FROM dbo.hb_view_lot_plan t1 FULL OUTER JOIN
dbo.hb_view_subdivision_plan t2 ON t2.plan_id =
t1.plan_id FULL OUTER JOIN
dbo.hb_view_business_unit_plan t3 ON t3.plan_id =
COALESCE (t1.plan_id, t2.plan_id) FULL OUTER JOIN
dbo.hb_view_organization_plan t4 ON t4.plan_id =
COALESCE (t1.plan_id, t2.plan_id, t3.plan_id)
ORDER BY t1.plan_no_name
Regards,
Don
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vo3o61t3hm1doo7ou1f1hoimq6nrdq5b07@.
4ax.com...
> On Sun, 24 Apr 2005 15:17:14 -0500, <dbj> wrote:
>
> (snip)
> Hi Don,
> Checking the code you posted, I see that you're not attempting to combine
> two tables, but a total of four! Now, whereas a full outer join betwee two
> tables is not very tough, a full outer join of three or more tables has
> some gotchas in the ON clause.
> Here's a simplified version of a four-way full outer join to show you how
> you should approach this:
> SELECT COALESCE (t1.keycol, t2.keycol, t3.keycol, t4.keycol),
> COALESCE (t1.datacol, t2.datacol, t3.datacol, t4.datacol)
> FROM Table1 AS t1
> FULL JOIN Table2 AS t2
> ON t2.keycol = t1.keycol
> FULL JOIN Table3 AS t3
> ON t3.keycol = COALESCE(t1.keycol, t2.keycol) -- Gotcha!
> FULL JOIN Table4 AS t4
> ON t4.keycol = COALESCE(t1.keycol, t2.keycol, t3.keycol) -- Gotcha!
> I'll leave it too you to fill this pattern with your (IMO much too long)
> table and column names.
> Oh, and by the way - TOP 100 PERCENT is totally meaningless; it clutters
> your query, and in the worst case introduces unnecessary overhead at
> execution time. Please remove it.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 24 Apr 2005 22:48:27 -0500, <dbj> wrote:
>Hi Hugo,
>Your suggestion worked. Thanks VERY MUCH for your help. And I would also
>like to thank everyone else who responded as well. I am sure my
>inexperience was apparent but really appreciate your patience helping me
>through this. Below is your code that worked!
>SELECT TOP 100 PERCENT COALESCE (t1.plan_id, t2.plan_id, t3.plan_id,
(snip)
Hi Don,
Good to hear that it worked. Now all that's left to do is to get rid of
the totally useless TOP 100 PERCENT. :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql
it with foreign key constraints|||In fact, computed columns may be indexed if certain conditions are met: