comparing parts in different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

comparing parts in different tables

Author

Topic

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-04-09 : 10:39:51

I have one table set up with 1000's of products like this:
id,part,price,stock - table has tons more info, but this is all that is important.

I created another table because recently we had some trouble, in which a part would get a wrong price and we would manually fix it. Well, I created a table for the fixed prices like this:
id,part,price,expdate

I joined the table on the id, that's simple. Since we have 5 different distributors, I created 5 different temp tables for the fixed prices - the design of the main fixed table is bad. Each temp tables is named per distrib. i.e.) dist1temp,dist2temp,etc. and each table only lists the prices that have an expdate >= getdate().

I am looking for a way to add into the original query a way to compare to the new temp tables.
If this is my original query:
select id,part,price,stock,price,stock,price,stock,etc... for 5 diff. price/stocks (5 diff. distrib's), what would be the addition before each 'price' that's selected to get the fix price if it exists.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-09 : 10:59:46

Without some sample data and expected output I am not able to get you - sorry.

No, you're never too old to Yak'n'Roll if you're too young to die.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 11:04:14

Having a separate table per distributor isn't a good idea.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-04-09 : 11:09:00

Let's say I have this so far:
id , part , price1 , stock 1, price2 , stock2 - goes to price5,stock5
aa , hffd , 400 , 30 , 200 , 20

Now, distributor 2 tells us their price is a mistake and we need to set it at 380. My fix table looks like this:
id , part , price1 , expdate1 , price2 , expdate2 - goes to price5,expdate5
aa , hffd , 0 , 1/1/1900 , 380 , 4/30/2010 - 0 and 1/1/1900 are defaulted

I break each fix down into temp tables based on 1/2/3/4/5 like this:
id , part , price1 , expdate1 - #temp1

id , part , price2 , expdate2 - #temp2
aa , hffd , 380 , 4/30/2010

now, my main table should look like this:
id , part , price1 , stock1 , price2 , stock2
aa , hffd , 400 , 30 , 380 , 20

The price changes in the main table only if a fix price exists in the corresponding #temp table per distributor.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 11:25:51

How about this:


UPDATE MainTable
SET Price1 = t.Price1
FROM MainTable m
INNER JOIN #temp1 t
	ON m.id = t.id

UPDATE MainTable
SET Price2 = t.Price2
FROM MainTable m
INNER JOIN #temp2 t
	ON m.id = t.id

UPDATE MainTable
SET Price3 = t.Price3
FROM MainTable m
INNER JOIN #temp3 t
	ON m.id = t.id

UPDATE MainTable
SET Price4 = t.Price4
FROM MainTable m
INNER JOIN #temp4 t
	ON m.id = t.id

UPDATE MainTable
SET Price5 = t.Price5
FROM MainTable m
INNER JOIN #temp5 t
	ON m.id = t.id

However, your table structure needs looking at. What happens if you need to add a new distributor? You'll need to add columns to your main table. You should have a table of Distributors, and a table of Distributor Prices.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-04-09 : 11:30:53

Looks good, although will anything update if nothing exists in the temp tables? It will only pull prices that have not expired into the temp tables, so if the temp table becomes blank, what will the update do?

And about the structure, we do have tables with prices and distributors. We go through about 10 temp tables to make the main table, but when a distributor says use this price, we need to use that as a final price, hence comparing it to the main table. It's probably still bad in design, but it's been working ok for now.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 11:42:53

quote:
Originally posted by detlion1643

Looks good, although will anything update if nothing exists in the temp tables? It will only pull prices that have not expired into the temp tables, so if the temp table becomes blank, what will the update do?

It only updates values that exist in the temp table. If the table is empty, it won't update anything.

quote:
And about the structure, we do have tables with prices and distributors. We go through about 10 temp tables to make the main table, but when a distributor says use this price, we need to use that as a final price, hence comparing it to the main table. It's probably still bad in design, but it's been working ok for now.

Bad designs usually work Ok for a while, that's one of the things that makes them so bad. If they didn't work from the beginning, nobody would use them. :)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources