Get data from two tables - 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 2005 Forums

Transact-SQL (2005)

Get data from two tables

Author

Topic

tbstar
Starting Member

2 Posts

Posted - 2008-12-01 : 13:43:07

Hi all,

I have two tables one to many, the client_tbl and client_history_tbl are linked with client_tbl_key. the child table holds history of service changes. now I would like to get all the clients that have a specific service, let's say 10 but I can't get the data that I want. Why? Because when I try Max of the date or the primary key of the child table, it doesn't give me the latest entry. I would've used Access Last function but it's not there. Any clue?

Clients

pk1
pk2
pk3

Services

pk1, fk1, 11/1/2008 1:30, 15
pk2, fk1, 11/10/2008 1:33, 10
pk3, fk1, 11/19/2008 1:34, 11
pk4, fk1, 12/1/2008 1:36, 20
pk5, fk1, 12/1/2008 1:38, 10

The result should look like this:

pk1, fk5

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 23:50:10

[code]SELECT c.PK,s.PK,s.Date,s.Value
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY c.PK ORDER BY h.Date DESC) AS Seq,*
FROM Clients c
INNER JOIN Services s
ON s.FK=c.PK
)t
WHERE t.Seq=1[/code]

tbstar
Starting Member

2 Posts

Posted - 2008-12-09 : 12:10:26

I found my way through using your approach, thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 12:14:19

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources