Can find the solution
-
I need some help with a query I'm writing
I got four tables with the fields:
Code:
Request (id, req_num, timestap, desc, userid)
Change (id, req_id, chg_num, timestamp, update_text)
Change_hist (id, chg_id, timestamp, action, comment)
User(userid, fname, lname)
A user log a request which most likely is split up into several work parts (change) and then for each update of the change its stored in the change_hist table.
The change_hist table has a action field which is used to track changes. Each change should go from 'open -> assigned' and then to 'assigned -> responded'. This is the important thing in this query...
What I'm trying to do is:
List out all users that has a request logged,
List total requests the user has logged
List total requests the user has logged that has been through the correct status change.
So I got the query:
Code:
select distinct
lname,
count(request.id)
from request
right join change on request.id = change.req_id
left join user on request.userid = user.userid
This gives me the correct total requests logged by each user, but when I add the change_hist field it messes it all up...
Any ideas about this?
-
Hi,
Try to use the group by clause. Because u r going to get count. so try to use those columns in group by clause, whatever present in select list.
I hope this will work
Saravanan.
Kindest Regards,Saravanan V
geocities.com -
Forgot to include the group statement:
Group by lname
So I get the following result:
Name Total
user1 5
user2 2
user3 4
But if I include the change_hist table like this:
left join change_hist on change.id = change_hist.chg_id
I get the following result:
Name Total
user1 14
user2 9
user3 12
So it looks like it adds all the change_hist stored as well...
-
Hi,
Try the following.
select distinct
lname,
count(request.id)
from request
left join change on request.id = change.req_id
left join user on request.userid = user.userid left join change_hist on change.id = change_hist.chg_id
I just changed the join condition. I replaced the right join with left join. please try this
Kindest Regards,Saravanan V
geocities.com -
The reason why I used a right join is because some requests doesn't have a change recorded, but that doesn't make a different though
Maybe I haven't really explained well what I'm trying to do...
the fields I need are:
lname,
count(request.id) AS [Total Requests],
count(request.id) AS [Correct Requests]
the two first columns are ok, but the third one is my issue.
I need to count all requests where the change_hist.action has entry = open -> assigned' AND 'assigned -> responded'
Still not able to get this right and need any advice at this stage
-
- LEFT JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
- RIGHT JOIN
A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.
and run the following query. Hopefully it will work.
Select lname,
count(request.id) AS [Total Requests],
count(Change_hist.chg_id) AS [Correct Requests]
from request
left join change on request.id = change.req_id
left join user on request.userid = user.userid left join change_hist on change.id = change_hist.chg_id
U need correct request value from change_hist only, then why r u using count(request.id) as [Correct Requests]. Try to run this, defenetly u will get the answer.
all the best.
Kindest Regards,Saravanan V
geocities.com - LEFT JOIN
-
Thanks for that but I don't think you really read my post...
I want to count the request id's for both total request created and total request's created that has been created in the correct order (ie. open -> assigned then assigned -> responded)
If I count with the change_hist table joined I get lots more counts because each request can have many changes and each change will have one entry for each change made to that change (hmmm..confused?)
-
SELECT u.lname,t.Total,ISNULL(c.Correct,0) AS [Correct]
FROM [User] u
INNER JOIN (SELECT u.userid,COUNT(*) AS [Total]
FROM [User] u
INNER JOIN [Request] r ON r.userid = u.userid
GROUP BY u.userid) t
ON t.userid = u.userid
LEFT OUTER JOIN (SELECT u.userid,COUNT(*) AS [Correct]
FROM [User] u
INNER JOIN [Request] r ON r.userid = u.userid
WHERE EXISTS(SELECT 1 FROM
[Change] c
INNER JOIN [Change_hist] h ON h.chg_id = c.id AND h.[action] = 'open'
INNER JOIN [Change_hist] h2 ON h2.chg_id = h.chg_id AND h2.[action] = 'assigned' AND h2.[timestamp] > h.[timestamp]
WHERE c.req_id = r.id)
AND EXISTS(SELECT 1 FROM
[Change] c
INNER JOIN [Change_hist] h ON h.chg_id = c.id AND h.[action] = 'assigned'
INNER JOIN [Change_hist] h2 ON h2.chg_id = h.chg_id AND h2.[action] = 'responded' AND h2.[timestamp] > h.[timestamp]
WHERE c.req_id = r.id)
GROUP BY u.userid) c
ON c.userid = u.userid
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks alot David. You saved my day with that
A few small changes had to be done, but your suggestion is spot on.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply