problem with tricky SELECT statement.

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)

problem with tricky SELECT statement.

Author

Topic

nickjones
Starting Member

16 Posts

Posted - 2003-12-09 : 12:18:00

I once again come to you guys for your expert help.

Here's the tables:

MainTable
-----------------------------
ID
Name
Description
Etc

Log
-----------------------------
ID
DateTime
MainTableID - link to the maintable ID
LogStatusID - link to the log status table

LogStatus
----------
ID
Name - name of the status

I want a query to pull out all the records in MainTable, plus the status of the most recent log entry. I.e. I want to return the following fields for each record:

MainTableID
MainTableName
MainTableDesc
LastLogStatusName

The clause to get the last Log can either use the datetime field to pull out the most recent, or the biggest Log ID field if that's easiest. The MainTable might not have any logs, so it needs to be an outer join on the the log table (i.e. the LastLogStatusName can be NULL)

I'm really stuck here... it's easy enough to get out the ID of the last log, or the datetime of the most recent log, but I'm stuck then using that to link to the logstatus.

Thanks in advance guys

Edit: I also need to be able to filter by LogStatus - e.g. only select records from MainTable WHERE LastLogStatusName = 'DELETE' etc. I presume that this would doesn't effect things but I thought I'd mention it :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-09 : 12:44:56

Perhaps if you provided DDL (CREATE TABLE) statements for your tables and DML for sample data (INSERT INTO). Once this is provided, you'll find that answers come very quickly on this site. I realize that you have provided the table information, but the format that you provided would mean that we would have to do the typing on our own. Please provide the needed information to get a quick answer.

Tara

nickjones
Starting Member

16 Posts

Posted - 2003-12-09 : 12:51:12

I'm rather pants at writing queries like that and I though that perhaps this question was rather generic and didn't need to be tested out (an answer might have been obvious to you experts and not needed it). I'm leaving work now but tomorrow morning I'll check on and do as you suggest. Thanks.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-12-09 : 13:59:58

The CREATE TABLE and INSERT INTO would be nice to test this but I think this will work:

SELECT	a.MaintTableID, a.MainTableName, a.MaintTableDesc, b.Name 'LastLogStatusName'
FROM	MainTable a LEFT JOIN
	(
	SELECT	z.MainTableID, y.Name
	FROM	(
		SELECT	MAX(ID) 'LogID'
		FROM	Log
		GROUP BY MaintTableID
		) x
	JOIN	Log z ON x.LogID = z.ID
	JOIN	LogStatus y ON z.LogStatusID = y.ID
	) b ON a.MainTableID = b.MainTableID

nickjones
Starting Member

16 Posts

Posted - 2003-12-10 : 06:58:06

drymchaser; thanks, that's just what I wanted. It was more the theory behind it (the idea of joining to the table to select the top ID, then joining that to the same table through MAX(ID) = ID) is what I wanted.

I'll make sure I post DDL & DML next time I come crawling for your expertise :P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources