Retrieve values from child table

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

SQL Server Development (2000)

Retrieve values from child table

Author

Topic

etinuz
Starting Member

4 Posts

Posted - 2005-02-02 : 11:45:32

Hello there,

I need to get the last value (status) from a child table. I try to simplify the problem with the following example.

Create Table Users
(
UserId int,
Lastname nvarchar(50)
)

Create Table UserStatus
(
UserId int,
Date datetime,
StatusId int
)

Create Table Status
(
StatusId int
Status nvarchar(50)
)

A user will go through all Status one by one. (1) Registered -> (2) In progress -> (3) authorized.
Now I want to know which users are in progress (2) but a simple select statement like:

Select LastName from Users Inner Join Users.UsersId = UserStatus.UserId Where UsersStatus.StatusId = 2

Will not return the wanted records because all authorized Users have been in this status.

I hope you understand the problem and can help me out.

Thx in advance.

Etinuz

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 11:57:40

Try the following:

SELECT
	u.Lastname
FROM
	Users u
	JOIN UserStatus us
		ON u.UserId = us.UserId
WHERE
	us.StatusId = @mystatus
	AND NOT EXISTS(	SELECT * FROM UserStatus us2
			WHERE u.UserId = us2.UserId
			AND us.StatusId < us2.StatusId )

rockmoose

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-02 : 11:58:42

Try
SELECT u.LastName
FROM Users u INNER JOIN UserStatus us ON u.UsersId = us.UserId
WHERE us.StatusId = 2 AND us.StatusId <> 3

Wrong again, now weres that yak with the gun!

Andy

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-02 : 12:02:55

[code]
SELECT LastName
FROM Users u
WHERE EXISTS (SELECT *
FROM UserStatus s1
WHERE s1.StatusId = 2
AND u.UserId = s1.UserId)
AND NOT EXISTS (SELECT *
FROM UserStatus s1
WHERE s1.StatusId = 3
AND u.UserId = s1.UserId)

[/code]

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 12:07:07

Hey Brett, what if I want only status 1 users ?!?

Happy hunting for the Yak Andy

!

rockmoose

etinuz
Starting Member

4 Posts

Posted - 2005-02-02 : 13:26:10

thx all for replying this fast.
I'll check your solution (rockmoose) later tonight, but the Users who have reached status 2 already have status 1.
Brett, rockmoose is right. In the application I have more options then 3 so filtering this way is not an option.

etinuz
Starting Member

4 Posts

Posted - 2005-02-03 : 10:54:53

I did get some suggestions (Rockmoose also your corrected suggestion is very close) but I am still not there. The values are not in a specific order (and can not be set this way). So the example was not set right. So a user can be Authorized (3) and later set to In progress (2). However in the table there is an ID or insert Date.

Create Table UserStatus
(
Id int, (identifier)
UserId int,
Date datetime,
StatusId int
)

At this moment I had the following suggestion.

Select Users.LastName From Users Inner Join (Select Max(ID) As MaxID, UserID, StatusId From UserStatus Group BY USerId, StatusId) As LimitedStatus On Users.UserID = LimitedStatus.UserID WHERE StatusID = 2

But this won’t return my latest StatusId but all Users which have been in StatusId 2 and not only those ones who has StatusId 2 as latest Status

Any suggestions?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-03 : 11:12:41

How about this then:

SELECT
	u.LastName
FROM
	Users u
	JOIN UserStatus us
		ON u.UserId = us.UserId
	JOIN
	(	SELECT UserId, MAX([Date]) AS last_date
		FROM UserStatus
		GROUP BY UserId ) AS last_status
		ON u.UserId = last_status.UserId
		AND us.[Date] = last_status.last_date
WHERE
	us.StatusId = @mystatus

Or this:

SELECT
	u.LastName
	,current_status = ( 	SELECT us.StatusId FROM UserStatus us
				WHERE us.UserId = u.UserId
				AND us.[Date] = last_status.last_date )
FROM
	Users u
	LEFT JOIN
	(	SELECT UserId, MAX([Date]) AS last_date
		FROM UserStatus GROUP BY UserId ) AS last_status
		ON u.UserId = last_status.UserId

rockmoose

etinuz
Starting Member

4 Posts

Posted - 2005-02-03 : 11:45:45

Yes this is working!! Thx a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources