Selecting record based on latest date 1

[lambic]

Hi,

Apologies if this has been covered before! I need to query some data from a SQL Server 200 database from 3 tables, with data from the 3rd table being selected on a date field.

Table structure is something like this:

Table_a
Property_ID
Company Name

Table_b
Property_ID
Property_Address1
Property_Address2
Property_Address3

Table_c
Property_ID
Property_Value
Value_Date

I need all data from Table_a & Table_b & just the latest data from Table_c based on the Value_Date field. The 3 tables are linked by Property_Id. I'm sure this is simple enough, but my brain is not functioning correctly today!

Many Thanks

Mark

 

[swampBoogie]

select CompanyName, 
Property_Adress1, 
Property_Value,
Property_date
from table_a a join table_b b
on a.property_id = b.property_id
join table_c c 
on b.property_id = c.property_id
where property_date
in (select max(property_date) 
   from table_c
  where property_id = c.property_id)

 

[r937]

swampboogie, why IN? why not equals?

the subquery will only ever return exactly one value, right?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[lambic]

Thanks swampboogie! It appears to have given me the results I need.

Cheers!

 

[swampBoogie]

I don't see any benefit of uinsg = and maybe the DBMS is not smart enough to avoid generating code for checking the cardinality.