Sql Help needed - 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 2000 Forums

Transact-SQL (2000)

Sql Help needed

Author

Topic

DavidRhodes
Starting Member

40 Posts

Posted - 2004-03-22 : 04:19:18

I'm stuck trying to create the following:

I have 2 tables [tClient] (ClientID, Name, Keywords) and [tRawData] (ClientID, Month) with a one-to-many relationship tRawData.ClientID is the foreign key.

I need to select all data from tClient along with the MAX(Month) from tRawData relating to each ClientID.

I can do it by creating a temporary table (ClientID, Name, Keywords, Month), inserting all from tClient into it then looping through and adding the Month from tRawData relating the ClientID but is there an easier way to do it?

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-22 : 04:35:44

Try this

SELECT c.ClientID, r.Month
FROM tClient c
LEFT JOIN (SELECT ClientID, MAX(Month) AS Month FROM tRawData GROUP BY ClientID) r ON c.ClientID = r.ClientID

Raymond

DavidRhodes
Starting Member

40 Posts

Posted - 2004-03-22 : 04:55:20

That's it, thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources