Select a value from another Table containing values but not in the same row

Question

Select a value from another Table containing values but not in the same row

h.sibilski

Newbie

Points: 8
More actions
September 2, 2017 at 7:23 am

#332459

Hello,
I'm a T SQL newbie and need some advice.
I have two tables:
1) consumed quantity
Key Quantity
aa 500
2) price list with different prices for kkey and consumed quantity
Key Quantity Price
aa 100 3.0
aa 600 2.50
aa 1000 2.5
In the case I need the price 2.50 . I have to find the price >= row1 and <= row2.
How to solve this problem in T SQL?
Sorry, but my English is very poor.
regs
Husibilski

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

anand08sharma SSC Enthusiast Points: 180 More actions · Answer 1

select table1.key, table2.price
from table1 left join table2 on (table1.key=table2.key)
where 2 = (select count( distinct price) mm from table2 where table1.price < table2.price);

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

-- sample data

;WITH

Consumed ([Key], Quantity) AS (SELECT 'aa', 500),

PriceList ([Key], Quantity, Price) AS (SELECT 'aa', 100, 3.0 UNION ALL SELECT 'aa', 600, 2.50 UNION ALL SELECT 'aa', 1000, 2.0)

-- solution

SELECT c.[Key], c.Quantity, x.Price

FROM Consumed c

CROSS APPLY (SELECT TOP 1 Price FROM PriceList pl WHERE pl.Quantity > c.Quantity ORDER BY pl.Quantity) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden