Help with UPDATE with Custom INNER JOIN - T-SQL

Question

I have a table for each location type (City, Street, Country, etc).

Now i have a stored procedure which needs to return a list of "Locations", with their "highest rated Review".

I have a temporary table to hold onto the "Locations" (could be anything - Street, City, etc).

I insert into the temp table first from Cities, then Streets, then Countries, etc.

I end up with a temp table of "Locations", and for each one of those i need to grab the highest rated Review. (highest = BaseScore, e.g 5.0)

Cardinalities:

• Location Many..Many Posts (PostLocations join table)
• Post 1..1 Review
• Review 1..1 Score

Location Temp Table:

DECLARE @ResultSet TABLE
(
    [LocationId]                INT,
    [TopReviewId]               INT, -- starts out NULL, need to fill in
    [TopReviewContent]          NVARCHAR(MAX)  -- starts out NULL, need to fill in       
)

-- Get the Top Rated Review for each location.
    UPDATE      ResultSet
    SET         TopReviewId = TopReview.PostId, 
                TopReviewContent = TopReview.Content
    FROM        @ResultSet ResultSet
    INNER JOIN
    (
        SELECT TOP 1 pl.LocationId, p.postid, p.Content
        FROM PostLocations pl
        INNER JOIN Posts p
        ON pl.PostId = p.PostId
        INNER JOIN Reviews r
        ON p.PostId = r.PostId
        INNER JOIN Scores s
        ON r.ScoreId = s.ScoreId
        INNER JOIN @ResultSet rs
        ON pl.LocationId = rs.LocationId
        ORDER BY s.BaseScore DESC
    ) AS TopReview
    ON  ResultSet.LocationId = TopReview.LocationId

    INNER JOIN  PostLocations pl
    ON          ResultSet.LocationId = pl.LocationId
    INNER JOIN  Posts p
    ON          pl.PostId = p.PostId
    INNER JOIN  Reviews r
    ON          pl.PostId = r.PostId


    -- Now return the Results:
    SELECT      TOP 10
                [LocationId],
                [TopReviewId],             
                [TopReviewContent]        

    FROM        @ResultSet

This is the output i am currently getting

LocationId    TopReviewId   TopReviewContent
1             12313         Blah Blah
2             NULL          NULL
3             NULL          NULL

This is the output i want

LocationId    TopReviewId   TopReviewContent
1             12313         Blah Blah
2             44323         Meh meh
3             5345345       Pew pew

Answer 1

Maybe this is naive but have you tried:

select c.name, max(price)
from 
    customer c inner join
    product p on p.customerid = c.customerid
group by c.name

if you want the single best review, you'd do this:

SELECT TOP 1 pl.LocationId, p.postid, p.Content
FROM PostLocations pl  
    INNER JOIN Posts p  
        ON pl.PostId = p.PostId  
    INNER JOIN Reviews r  
        ON p.PostId = r.PostId  
    INNER JOIN Scores s  
        ON r.ScoreId = s.ScoreId  
ORDER BY s.BaseScore DESC  

If you want the best review per location, you'd do this:

SELECT pl.LocationId, p.postid, p.Content, max(s.BaseScore)
FROM PostLocations pl  
    INNER JOIN Posts p    
    ON pl.PostId = p.PostId  
    INNER JOIN Reviews r  
    ON p.PostId = r.PostId  
    INNER JOIN Scores s  
    ON r.ScoreId = s.ScoreId  
group by p1.locationid, p.postid, p.content  

Answer 2

I am bit confused as to what you are trying to do. but to get the list of customers along with their highest order ID, couldn't you just do this?

select customers.name, orders.orderid 
from customers join orders on customers.customerid = orders.customerid 
group by customers.customerid 
having max(orders.price);