Joining tables with date filtering

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 2000 Forums

Transact-SQL (2000)

Joining tables with date filtering

Author

Topic

Jozef Selesi
Starting Member

5 Posts

Posted - 2002-10-03 : 10:07:03

Hello,
I'm having trouble with a SELECT statement. Here are the relevant tables with some sample data:


Table1:
ProductID (PK, int) ... [other columns]
   1
   2
   3

Table2:
ProductID (int)  Date (datetime) ... [other columns]
   1               25/06/2002
   2               23/05/2002
   3               29/08/2002
   2               10/09/2002
   2                3/10/2002
   1                2/10/2002
   3               29/09/2002

I need a single record with the latest date from Table2 for each row in Table1. The result should look like this:


ProductID        Date       ...
   1           2/10/2002
   2           3/10/2002
   3          29/09/2002

I can join the two tables, but I can't filter out the records from Table2 with older dates. Do you have any ideas?

Best regards,
Jozef

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-03 : 10:17:57

There are a couple ways to do this.

Use a GROUP BY clause and the MIN() function on your date

or

Use a WHERE NOT EXISTS (correlated subquery) construct

Jay White
{0}

Jozef Selesi
Starting Member

5 Posts

Posted - 2002-10-03 : 17:45:08

Thank you for your reply. Maybe I should have mentioned that I'm not very adept in SQL, and I haven't worked much with complex queries. I tried your first suggestion and managed to get the records with the latest dates from the second table. Here is the query I wrote (ProductInfo is Table1 from my previous post, and Prices is Table2):

SELECT x.ProductID, MAX(y.Date)
FROM ProductInfo AS x, Prices AS y
GROUP BY x.ProductID

With this query I did get the exact recordset I asked for in my last post, but, unfortunately, I didn't explain what I need very well. The problem is that I need some columns from both tables, too (for example, Name from ProductInfo and Price from Prices). This seemed simple, so that's why I didn't go into details. I made a mistake in my previous post in the final recordset example. The Date field doesn't actually need to be in it, it should just be used to find the relevant record in the Prices table.

Each time a product price is changed, a new record is inserted into the Prices table, so it contains a history of prices for each product. The recordset I'm trying to get should contain a list of current prices for all products. I hope I was more a bit more clear now.

Best regards,
Jozef

(edit) I had to move the example to the end of the post, because the rest of it after the [ code ] tag doesn't show properly in IE6. (/edit)


ProductInfo
ProductID (PK, int), Name (nvarchar) ... [other columns]
   1                  'product1'
   2                  'product2'
   3                  'product3'

Prices
ProductID (int)  Date (datetime) Price (money) ... [other columns]
   1               25/06/2002    10
   2               23/05/2002    15
   3               29/08/2002    20
   2               10/09/2002    17
   2                3/10/2002    19
   1                2/10/2002    12
   3               29/09/2002    18

Result:
ProductID     Name      Price    [other columns from both tables may be needed in the recordset]
   1       'product1'    12
   2       'product2'    19
   3       'product3'    18

Edited by - Jozef Selesi on 10/03/2002 17:50:25

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources