Joining three tables

  • I have the following sql statement.

    SELECT m.[property_id], m.[Address], m.[City], m.[Zip], m.title_amt, m.ModifiedBy, m.County, m.Bedrooms, m.Bathrooms, m.HalfBathrooms, m.Title, m.HOA, m.Subdivision, m.Open_bid, m.ModifiedOn, i.imagepaththumb

    FROM [AUC_Property] m

    Left JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY property_id_fk ORDER BY property_id_fk) AS Rn,* FROM AUC_Image) i

    On property_id = property_id_fk

    AND Rn=1

    where Active_Month='Y'

    ORDER BY m.ModifiedOn desc, m.[Address] ASC

    However, I've recently split a table and made into three tables. What's the easiest way i can rewrite this if m.hoa, m.open_bid is now apart of the fin_property f table. They all have property_id to link each other. Can someone please provide me with insight on this issue.

  • have a look at this post http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Forum Etiquette: How to post data/code on a forum to get the best help

    By Jeff Moden,

    post in your new table structure and dummy data and i will help you

    ***The first step is always the hardest *******

  • Here is how I would do it. I in SQL 2008 R2 you can have as many JOINs as you have resources to deal with them. 3 is fairly small. Changes are underlined.

    SELECT m.[property_id], m.[Address], m.[City], m.[Zip], m.title_amt, m.ModifiedBy,

    m.County, m.Bedrooms, m.Bathrooms, m.HalfBathrooms, m.Title, f.HOA, m.Subdivision,

    f.Open_bid, m.ModifiedOn, i.imagepaththumb

    FROM [AUC_Property] m

    JOIN [Fin_Property] f

    ON f.Property_Id = m.Property_Id

    LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY property_id_fk ORDER BY property_id_fk) AS Rn,* FROM AUC_Image) i

    ON property_id = property_id_fk

    AND Rn=1

    WHERE Active_Month='Y'

    ORDER BY m.ModifiedOn DESC, m.[Address] ASC

    However as SGT_squeequal said, it would really help if we had table structures to work with.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

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

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