Subquery problem - 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

General SQL Server Forums

New to SQL Server Programming

Subquery problem

Author

Topic

obinna
Starting Member

26 Posts

Posted - 2007-06-15 : 05:17:58

I have a Property Database with 2 tables
Table Property = PropertyID (PK), PropertyStreetname etc
Table PropertyTracker = PropertyID (FK),Price, PropertyPriceID(PK)

So I have One To Many RelationShip with Property Table and PropertyTracker

How Do I get the current PropertyPrice from the two tables, my sub query fails

SELECT dbo.tbl_Property.PropertyID,
PRICES =
(
SELECT Price
FROM dbo.tbl_PropertyTracker
WHERE PropertyID = PropertyID
)

FROM dbo.tbl_PropertyTracker INNER JOIN
dbo.tbl_Property ON dbo.tbl_PropertyTracker.PropertyID =
dbo.tbl_Property.PropertyID

This is the error
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-15 : 05:22:58

What do you mean by "Current" property price? Is there any date field in PropertyTracker table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-15 : 05:27:47

So you want to maintain a datetime column in the PropertyTracker table so that it will be easy to get the latest price of the property.

If ur sure that the price of the property keep increasing then no need of datetime column, max price will give you the latest price.

--------------------------------------------------
S.Ahamed

obinna
Starting Member

26 Posts

Posted - 2007-06-15 : 05:32:04

Yes in the PropertyTracker Table I have DateAdded, IsCurrent which is Bit, I want to retrieve the last Price inserted on the PropertyTracker. I hope I am clear
Thanks

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-15 : 05:38:36

Select * from dbo.tbl_Property P join
(SELECT PropertyID, datecol, price
FROM dbo.tbl_PropertyTracker PT
where datecol = (Select max(datecol) from dbo.tbl_PropertyTracker where propertyid = Pt.propertyid)
group by PropertyID ) spt
on p.PropertyID = spt.PropertyID

--------------------------------------------------
S.Ahamed

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-15 : 05:41:30

[code]Select p.PropertyId, pt.Price
From tbl_Property p JOIN tbl_PropertyTracker pt
on p.PropertyID = pt.PropertyID
Join
(select PropertyID, max(DateAdded) as DateAdded
From tbl_PropertyTracker
Group by PropertyID
) t
on pt.PropertyID = t.PropertyID and pt.DateAdded = t.DateAdded[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources