CODEWITHSUNDEEP
c#sql-serverjoinpagination
Kieran
Kieran

Reputation: 656

Which Join is needed for this query in SQL Server

I need to return paginated results i.e the second five records from a table called properties. This works alone with this query:

SELECT  
    Property_ID, 
    Property_Type, 
    Address_Line, 
    Area, 
    Postcode, 
    Weekly_Rate 
FROM
    dbo.Properties 
WHERE   
    Area LIKE '%" + value + "%' 
ORDER BY 
    Property_ID 
    OFFSET " + (start*end) + " ROWS 
    FETCH NEXT " + end + " ROWS ONLY"

The above code is written with C# added.

But I also need the first Image ID from the Images table that references each record returned in the paginated subset.

I tried a FULL OUTER JOIN and it didn't return what was expected, maybe because there is more than one image per property.

Any solutions to this would be great!

Thanks

This seems to nearly work but it's returning an error:

SELECT 
    dbo.Properties.Property_ID, Property_Type, dbo.Images_Table.[URL]
FROM 
    dbo.Properties p 
OUTER APPLY
    (SELECT TOP 1 i.*
     FROM dbo.Images_Table i
     WHERE i.Property_ID = p.Property_ID
     ORDER BY i.Image_ID) i
WHERE 
    p.Area LIKE '%po%' 
ORDER BY 
    p.Property_ID 
    OFFSET 0 ROWS 
    FETCH NEXT 5 ROWS ONLY

This is the error returned :

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Properties.Property_ID" could not be bound.

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Images_Table.URL" could not be bound.

Upvotes: 0

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You can use outer apply:

SELECT . . . , i.??
FROM dbo.Properties p OUTER APPLY
     (SELECT TOP 1 i.*
      FROM images i
      WHERE i.Property_ID = p.Property_ID
      ORDER BY i.Image_ID
     ) i
WHERE p.Area LIKE '%" + value + "%' 
ORDER BY p.Property_ID 
OFFSET " + (start*end) + " ROWS 
FETCH NEXT " + end + " ROWS ONLY";

This is a bit speculative on how you are ordering the images. You also need to fill in the columns you want from the images table.

Upvotes: 1

Related Questions