Trying to construct simple SQL query

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

SQL Server 2000 Forums

SQL Server Development (2000)

Trying to construct simple SQL query

Author

Topic

token
Posting Yak Master

133 Posts

Posted - 2007-06-10 : 11:30:31

Hi guys,

Sorry if this seems overly simple, but I can't quite get my head around how to achieve a simple INSERT between two tables.

I have a table called PRESTAGE which contains loads of rows of products. The table is laid out in four colums so you have ID, Manufacturer, Code, Cost.

So the contents look like this:

1230, Acer, LX109, 100.00
1231, Acer, LX109, 200.00
1232, Acer, LX109, 250.00
1500, Sony, CS224, 998.00
1501, Sony, CS224, 1050.00

I have another table called STAGE which has exactly the same structure as PRESTAGE. But I would like to INSERT into STAGE FROM PRESTAGE the LOWEST value in the Cost field for each Manufacturer and Code combination.

So I would expect to see in the STAGE table only the cheapest rows for each unique Manufacturer and Code combination:

1230, Acer, LX109, 100.00
1500, Sony, CS224, 998.00

Does anyone have idea how this achieveable? I have spent quite some time writing it down on paper but it never works out for me logically.

I'm very grateful for any help.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-10 : 12:12:06

[code]

Select Id, t2.manufacturer, T2.code, T1.mincost
from @t T2 join (
select manufacturer, code, min(cost) as MinCost
from @t group by Manufacturer , Code
) T1 on t1.Manufacturer = t2.Manufacturer and t1.Code = t2.Code and t1.mincost = t2.Cost
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

token
Posting Yak Master

133 Posts

Posted - 2007-06-10 : 12:45:34

This going to sound really stupid but...

what does the @t mean?

I have never used the @ symbol in an SQL query before

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-10 : 13:55:41

I have used a table variable to test your code. Read up books on line for more info about table variables. It can help you in future.
To get your code working, replace the @t with your table name.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

token
Posting Yak Master

133 Posts

Posted - 2007-06-10 : 14:58:37

Hi dinakar

Thanks for the code suggestion but it doesn't seem to work. It still inserts mutiple rows into the STAGE table from the PRESTAGE table for each item that has more than 1 price.

I think I made my example too simple. Its only the Manufacturer and ManfProductCode that should have the same combination in the table. Everything else is variable including the Supplier. Below are example rows from a row in the PRESTAGE column (it is laid out as ProductID, Manufacturer, ManfProductCode, ProductName, Supplier, SupplierProductCode, CostPrice, Availability)

01, ACER, LX1901, Aspire Laptop, SupplierUK, M150917, 183.10, 50
02, ACER, LX1901, Aspire Laptop, SupplierUSA, M150917, 190.50, 0
03, ACER, LX1901, Aspire Laptop, SupplierASIA, M150917, 205.00, 2

So the above rows are all in the PRESTAGE table. I would like to write a query that ensure only the CHEAPEST row (the lowest number in the CostPrice column) is INSERTED into the STAGE table.

So in the STAGE table if I searched for 'LX1901' as ManfProductCode, I would like to see:

01, ACER, LX1901, Aspire Laptop, SupplierUK, M150917, 183.10, 50

The following code works fine at just returning the cheapest row per Manufacturer and ManfProductCode combination, but all the other variables get screwed up and don't insert correctly e.g. incorrect Supplier gets inserted. Here is the code that sort-of works:

INSERT INTO Stage
(Manufacturer
,ManfProductCode
,ProductName
,Supplier
,SupplierProductCode
,CostPrice
,Availability
)
SELECT
b.Manufacturer
,a.ManfProductCode
,b.ProductName
,b.Supplier
,b.SupplierProductCode
,a.CostPrice
,b.Availability

FROM
(
Select Min(ProductID) AS ProductID, Min(CostPrice)AS CostPrice, ManfProductCode From
PreStage Group by ManfProductCode
) As a
Inner Join PreStage b on b.ProductID = a.ProductID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 09:07:13

add this at the end

and b.CostPrice=a.CostPrice

Madhivanan

Failing to plan is Planning to fail

token
Posting Yak Master

133 Posts

Posted - 2007-06-11 : 09:51:12

hi madhivanan

thanks for the suggestion. when i add the code you suggested, it doesn't insert as many rows as it should from PRESTAGE to STAGE. It inserts at least 1000 less rows which means not all the products will display.

Here is the data in the PRESTAGE table:
01, ACER, LX1901, Aspire Laptop, SupplierUK, M150917, 183.10, 50
02, ACER, LX1901, Aspire Laptop, SupplierUSA, M150917, 190.50, 0
03, ACER, LX1901, Aspire Laptop, SupplierASIA, M150917, 205.00, 2

Using my original code, this is inserted into the STAGE table:
01, ACER, LX1901, Aspire Laptop, SupplierUSA, M150917, 183.10, 0

So as you can see, it does insert the cheapest price (183.10) but it doesn't insert everything else in that row correctly. The supplier name should be SupplierUK not SupplierUSA and the availabilty should be 50 not 0.

Why can't it just insert the cheapest row with all its corresponding field values into the STAGE table from PRESTAGE. It sounds so simple but it is very hard to achieve!!

Thank you for any help, I am really very grateful.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-11 : 11:57:27

The code I provided should have given you some direction so you could have put some effort on your own..

Here's how you'd modify your code: If it still "doesnt work" post more data from your table and expected output.


Declare @t Table (ProductID int, Manufacturer varchar(50), ManfProductCode varchar(50), ProductName varchar(50), Supplier varchar(50), SupplierProductCode varchar(50), CostPrice decimal(10,2), Availability int)
Insert into @t 
Select 01, 'ACER', 'LX1901', 'Aspire Laptop', 'SupplierUK', 'M150917', 183.10, 50 union all
Select 02, 'ACER', 'LX1901', 'Aspire Laptop', 'SupplierUSA', 'M150917', 190.50, 0 union all
Select 03, 'ACER', 'LX1901', 'Aspire Laptop', 'SupplierASIA', 'M150917', 205.00, 2


Select ProductID, t2.manufacturer, t2.ManfProductCode,t2.ProductName,t2.Supplier,t2.SupplierProductCode, T1.mincost
from @t T2 join (
	select manufacturer, ManfProductCode,ProductName,SupplierProductCode,  min(CostPrice) as MinCost 
	from @t  group by manufacturer, ManfProductCode,ProductName,SupplierProductCode
	) T1 on t1.Manufacturer = t2.Manufacturer 
		and t1.ManfProductCode = t2.ManfProductCode 
		and t1.ProductName = t2.ProductName 
		and t1.SupplierProductCode = t2.SupplierProductCode 
		and t1.mincost = t2.CostPrice

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources