May 21, 2012 at 1:25 pm
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.
May 21, 2012 at 1:39 pm
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 *******
May 21, 2012 at 2:19 pm
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