CODEWITHSUNDEEP
c#sqlrazorforeachwebmatrix
Gavin5511
Gavin5511

Reputation: 791

WebMatrix - Inner Join and Foreach loop

i'm trying to do something which i see a lot, but im having trouble working out the best way to do it.

I have the following query:

"SELECT * FROM Property_Info INNER JOIN RateInfo ON Property_Info.PropertyID=RateInfo.RateID"

From this, i do the following foreach loop to show all my properties.

foreach(var row in queryResults){
<div class="row">
    <h4>@row.PropertyName</h4>
    <h5>Prices from: xxxx</h5>
    </div>
}

The problem i have, is that i need to do a "SELECT TOP 1" on the joined table (RateInfo) to show the lowest price for each property. I need to show this in the foreach loop where i have put xxxx

Do i need to write a seperate SQL query? if so, how do i bind it to the appropriate entry in the foreach loop.

Table Info:

Property_Info Table - PropertyID, PropertyName

RateInfo Table - PropertyID (this matches the PropertyID from the other table), RateID, RateName, RateValue

Upvotes: 2

Views: 353

Answers (2)

Polynomial
Polynomial

Reputation: 3706

SELECT
    pi.PropertyID,
    pi.PropertyName,
    MIN(ri.Rate) AS LowestRate,
    MAX(ri.Rate) AS HighestRate
FROM
    Property_Info pi
INNER JOIN
    RateInfo ri ON pi.PropertyID = ri.PropertyID
GROUP BY
    pi.PropertyID,
    pi.PropertyName

Changes from your code are using aliasing on the table names so that they are easier to refer to again and using a GROUP BY to let us perform aggregate functions on the data in the RateInfo table. Hopefully that's what you needed?

Upvotes: 1

simon at rcl
simon at rcl

Reputation: 7344

Without knowing the table structures it's a bit difficult, but I am assuming that RateInfo as a unique Id for each row. That being the case:

SELECT * FROM Property_Info pi
cross JOIN RateInfo ri
where ri.RateId = (select max(ri2.RateId) from RateInfo ri2 where ri2.RateId = pi.PropertyId)

...or instead of the max(ri2.Id) you could use:

select top 1 ri2.RateId from RateInfo ri2...

Cheers -

SELECT * FROM Property_Info pi cross JOIN RateInfo ri where ri.Id = (select max(ri2.Id) from RateInfo ri2 where ri2.RateId = pi.PropertyId)

Note the ri.Id = (select max(ri2.Id) .... This Id is the unique field I assume exists on RateInfo. Apologies..

Upvotes: 1

Related Questions