Joining three tables together - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Joining three tables together

Author

Topic

spsubaseball
Starting Member

17 Posts

Posted - 2012-05-21 : 13:48:56

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.

X002548
Not Just a Number

15586 Posts

Posted - 2012-05-21 : 14:59:21

create a view and make them look like and named as the original table?

spsubaseball
Starting Member

17 Posts

Posted - 2012-05-21 : 15:48:12

well i've been trying this:

select f.hoa, f.open_bid,
(SELECT m.[property_id], m.[Address], m.[City], m.[Zip], m.ModifiedBy, m.County, m.Bedrooms, m.Bathrooms, m.HalfBathrooms, m.Subdivision, 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 m.property_id = property_id_fk AND Rn=1
where Active_Month='Y') as test
from fin_property f

but I'm getting an error with "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources