September 12, 2012 at 8:09 am
Hi,
I am trying to join about 7 tables at the minute. Most of the joins work perfectly but I am trying to join a table which has a one - many relationship but only select the most recent date from a field.
SELECT
a.id,
a.new_id,
a.add_id,
b.add_1,
b.add_2,
b.add_3,
c.town,
d.county,
e.val,
e.date
FROM TBL a
LEFT JOIN TBL_ADD b ON a.addir = b.addid
LEFT JOIN TBL_TOWN c ON b.town_id = c.town_id
LEFT JOIN TBL_COUNTY d ON b.county_id = d.county_id
LEFT JOIN TBL_VAL e ON a.id = e.id
WHERE e.date IN (SELECT MAX (a.date) FROM TBL_VAL a JOIN PAD_PROPERTY b ON a.id = b.id GROUP BY a.pr_id)
It is selecting the most recent date but I am currently receiving almost twice the records I expect so I think I am getting confused with my joins - any help would be appreciated. If there is a better way to select records using most recent date let me know - I also tried using ROW_NUMBER but without any success
September 12, 2012 at 8:17 am
Your query for MAX(Date) should be linked somehow to entities you are querying above.
I'm not 100%, may be that way:
ELECT
a.id,
a.new_id,
a.add_id,
b.add_1,
b.add_2,
b.add_3,
c.town,
d.county,
e.val,
e.date
FROM TBL a
LEFT JOIN TBL_ADD b ON a.addir = b.addid
LEFT JOIN TBL_TOWN c ON b.town_id = c.town_id
LEFT JOIN TBL_COUNTY d ON b.county_id = d.county_id
LEFT JOIN (SELECT v.pr_id, MAX (v.date) mxDate
FROM TBL_VAL v
JOIN PAD_PROPERTY b
ON v.id = b.id
GROUP BY v.pr_id ) m
ON m.pr_id = a.id -- ? you need to link it some how to above tables
LEFT JOIN TBL_VAL e ON a.id = m.pr_id and e.date = m.mxDate
)
September 12, 2012 at 8:22 am
There's something screwy about this - you're aggregating on pr_id but expecting the max date per id. Try isolating the part which generates data for TBL_VAL and working with it like so:
SELECT
a.id,
a.date,
a.val,
rn = ROW_NUMBER() OVER(PARTITION BY a.pr_id ORDER BY a.date DESC)
FROM TBL_VAL a
JOIN PAD_PROPERTY b
ON a.id = b.id
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 12, 2012 at 8:49 am
It's because the SQL I copied into the forum post was wrong - I had too many windows open ...
Anyway the following SQL is what I had been attempting:
SELECT
a.pr_id,
a.bp_id,
a.address_id,
b.ADDRESS_LINE_1,
b.ADDRESS_LINE_2,
b.ADDRESS_LINE_3,
c.TOWN,
d.COUNTY,
e.COUNTRY,
b.POSTCODE,
b.UPRN,
b.GRID_E,
b.GRID_N,
v.val_asset,
v.date_asset,
v.val_asset_basis,
v.date_next_val
FROM dbo.TBL_PROPERTY AS a
LEFT JOIN dbo.TBL_ADDRESS AS b ON a.address_id = b.ADDRESS_ID
LEFT JOIN dbo.TBL_TOWN AS c ON b.TOWN_ID = c.TOWN_ID
LEFT JOIN dbo.TBL_COUNTY AS d ON b.COUNTY_ID = d.COUNTY_ID
LEFT JOIN dbo.TBL_COUNTRY AS e ON b.COUNTRY_ID = e.COUNTRY_ID
LEFT JOIN dbo.TBL_VALUATIONS v ON a.pr_id = v.pr_id
WHERE v.date_asset IN
(SELECT MAX(a.date_asset)
FROM TBL_VALUATIONS a LEFT JOIN TBL_PROPERTY b
ON a.pr_id = b.pr_id
GROUP BY a.pr_id)
That was stupid of me...i'll try the suggestions just now.
September 12, 2012 at 9:23 am
I think there's still a bit missing from the last part of your query. The final join to your max query is missing the ON.
Some DDL for the tables and sample data would help, too. Here's an article about how to post data/code to get the best help:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
September 12, 2012 at 9:46 am
Yeah apologies again I copied the sql whilst half way through attempting something else...
I edited my post above but the following is produces about double my expected results...
SELECT
a.pr_id,
a.bp_id,
a.address_id,
b.ADDRESS_LINE_1,
b.ADDRESS_LINE_2,
b.ADDRESS_LINE_3,
c.TOWN,
d.COUNTY,
e.COUNTRY,
b.POSTCODE,
b.UPRN,
b.GRID_E,
b.GRID_N,
v.val_asset,
v.date_asset,
v.val_asset_basis,
v.date_next_val
FROM dbo.TBL_PROPERTY AS a
LEFT JOIN dbo.TBL_ADDRESS AS b ON a.address_id = b.ADDRESS_ID
LEFT JOIN dbo.TBL_TOWN AS c ON b.TOWN_ID = c.TOWN_ID
LEFT JOIN dbo.TBL_COUNTY AS d ON b.COUNTY_ID = d.COUNTY_ID
LEFT JOIN dbo.TBL_COUNTRY AS e ON b.COUNTRY_ID = e.COUNTRY_ID
LEFT JOIN dbo.TBL_VALUATIONS v ON a.pr_id = v.pr_id
WHERE v.date_asset IN
(SELECT MAX(a.date_asset)
FROM TBL_VALUATIONS a LEFT JOIN TBL_PROPERTY b
ON a.pr_id = b.pr_id
GROUP BY a.pr_id)
September 12, 2012 at 9:54 am
It would help to have some test data (see referenced article).
But here is something to check. You're not relating your maximum asset date to the pr_id that each date is associated with. Effectively, you're saying 'if any property has the maximum asset date that I found for one property, include it in the result set'.
You might also want to review whether you really need all of those LEFT JOINS. Should some or all of them be INNER JOINS?
September 12, 2012 at 9:56 am
Have a look my example. The problem is here:
LEFT JOIN dbo.TBL_VALUATIONS v ON a.pr_id = v.pr_id
WHERE v.date_asset IN
(SELECT MAX(a.date_asset)
FROM TBL_VALUATIONS a LEFT JOIN TBL_PROPERTY b
ON a.pr_id = b.pr_id
GROUP BY a.pr_id)
You have no link between MAX(a.date_asset) and ID!
You shouldn't have your "SELECT MAX..." sub-query in WHERE clause, but JOIN to it, before JOINING to dbo.TBL_VALUATIONS in your main query:
LEFT JOIN (SELECT a.pr_id, MAX(a.date_asset) date_asset
FROM TBL_VALUATIONS a LEFT JOIN TBL_PROPERTY b
ON a.pr_id = b.pr_id
GROUP BY a.pr_id) mx
ON mx.pr_id = a.pr_id
LEFT JOIN dbo.TBL_VALUATIONS v
ON a.pr_id = v.pr_id AND v.date_asset = mx.date_asset
-- you can add this filter to ensure that records are only
-- returned when there is a record in TBL_VALUATIONS for an ID
WHERE v.pr_id IS NOT NULL
September 12, 2012 at 10:12 am
Thanks Eugene,
That's perfect, It has produced almost the exact number of records I expected. It is out by less than 10 records but I can see these records are duplicated as the date_asset is the same - silly users.
Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply