complicated Select - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

complicated Select

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2010-06-05 : 08:07:11

Hi All,

I'm Trying to make this select.
I got table

Table
Productid,price
1,10,00
2,23,00
3,24,00
4,35,00
5,5,00
6,0,50
7,0,95
8,2,00
9,1,00
10,0,25

And i got a record set:

price
10,00
50,00
3,00

now i want to pick from the table a record set with relation to the prices ex:
For the first record it would return this

Productid,price,Quanity
1,10,00,1

Not the trick part, since the other 2 records do not have an exact relation a want it to do this:

Productid,price,Quanity
1,10,00,1
1,10,00,5 (this would match the 50 of the 2nd record)
8,2,00,1 (these 2 would match the 3,00 for the 3 record)
9,1,00,1

Any help would be great! Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-05 : 10:40:43

quote:

Posted - 06/05/2010 : 08:07:11
--------------------------------------------------------------------------------

Hi All,

I'm Trying to make this select.
I got table

Table
Productid,price
1,10,00
2,23,00
3,24,00
4,35,00
5,5,00
6,0,50
7,0,95
8,2,00
9,1,00
10,0,25

And i got a record set:

price
10,00
50,00
3,00

now i want to pick from the table a record set with relation to the prices ex:
For the first record it would return this

Productid,price,Quanity
1,10,00,1

Not the trick part, since the other 2 records do not have an exact relation a want it to do this:

Productid,price,Quanity
1,10,00,1
1,10,00,5 (this would match the 50 of the 2nd record)
8,2,00,1 (these 2 would match the 3,00 for the 3 record)

WHY?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mike13
Posting Yak Master

219 Posts

Posted - 2010-06-05 : 11:07:24

I need it ;-)

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-05 : 11:39:05

no...WHY do you say that

(this would match the 50 of the 2nd record)

I guess HOW my be a better question

HOW do you know that to be TRUE?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mike13
Posting Yak Master

219 Posts

Posted - 2010-06-05 : 11:59:38

aaah sorry ;-)

1,10,00,5 (this would match the 50 of the 2nd record)

Quantity is 5, so it is 5x10=50

mike13
Posting Yak Master

219 Posts

Posted - 2010-06-07 : 02:25:48

Can anyone help with this please.
Or is what i ask simple impossible?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-06-07 : 08:21:28

I don't understand the logic that you have going there. Can you break it down again please.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-07 : 11:17:20

I think what people want to see is the logic described in words.

Something like:
1. For a given price (I.E: 10,00) find the first matching price in Table (where first is defined as the order of ProductID. For example:

I expect to match 10,00 with Table row 1, 10,00 because 10,00 is an exact match and 1 is the First occurrence of 10,00 in Table.

2. There there is not an exact match then ....

mike13
Posting Yak Master

219 Posts

Posted - 2010-06-08 : 02:10:21

I will try my best.
Let me explain the reason i want this.
I got big product table (+1000 products) and a customer made an order.
Now i have to translate this order to another table(+30) with very few products and not all the prices. so i need to pick other products and amount to make the same order amount.

And i got a record set:

(old order)
amount,price
1,10,00
1,50,00
1,3,00

now i want to pick from the (new smaller product table) table a record set with relation to the prices ex:
For the first record it would return this

Productid,price,Quantity
1,10,00,1

Not the trick part, since the other 2 records do not have an exact relation a want it to do this:

Productid,price,Quanity
1,10,00,1
1,10,00,5 (this would match the 50 of the 2nd record)
8,2,00,1 (these 2 would match the 3,00 for the 3 record)

On the other hand thinking about it i do not really need the 3 products, it even could become 2 or 1 product in the new order. if the total amount is just the same.

thanks a lot

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-08 : 02:25:05

what happen to productid 2, 3, 4, 5, 6, 7, 10 ? These are ignore ?

And how does the Quantity comes about ?

KH
[spoiler]Time is always against us[/spoiler]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-06-08 : 02:41:22

I am finding it tough to understand. How did "1,3,00" from your "(old order)" come down to "8,2,00,1 (these 2 would match the 3,00 for the 3 record)"??

mike13
Posting Yak Master

219 Posts

Posted - 2010-06-08 : 05:43:56

quote:
Originally posted by khtan

what happen to productid 2, 3, 4, 5, 6, 7, 10 ? These are ignore ?

And how does the Quantity comes about ?

KH
[spoiler]Time is always against us[/spoiler]

you got this record set
(old order)
amount,price
1,10,00
1,50,00
1,3,00
which is used to pick from new table, nothing todo with old big table

mike13
Posting Yak Master

219 Posts

Posted - 2010-06-08 : 05:46:44

quote:
Originally posted by sakets_2000

I am finding it tough to understand. How did "1,3,00" from your "(old order)" come down to "8,2,00,1 (these 2 would match the 3,00 for the 3 record)"??

upps i made an error there

should be:

8,2,00,1 (these 2 would match the 3,00 for the 3 record)
9,1,00,1

mike13
Posting Yak Master

219 Posts

Posted - 2010-06-09 : 02:14:27

Is it possible to do it or not? should i look for another approach?

mike13
Posting Yak Master

219 Posts

Posted - 2010-06-16 : 06:16:18

Is it possible to do it or not? should i look for another approach?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-06-16 : 07:39:09

The main problem seems to be that nobody understands what you want to achieve. Follow the HOW TO ASK link, and restate your question.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources