Can find the solution

Question

Post reply

Can find the solution

dbwhz

Old Hand

Points: 328
More actions
November 15, 2005 at 7:51 pm

#166214

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?
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

Saravanan V SSC Enthusiast Points: 156 More actions · Answer 1

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

dbwhz Old Hand Points: 328 More actions · Answer 2

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...

Saravanan V SSC Enthusiast Points: 156 More actions · Answer 3

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

dbwhz Old Hand Points: 328 More actions · Answer 4

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

Saravanan V SSC Enthusiast Points: 156 More actions · Answer 5

Saravanan V

SSC Enthusiast

Points: 156

November 16, 2005 at 4:20 am

#604142

dbwhz Old Hand Points: 328 More actions · Answer 6

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?)

David Burrows SSC Guru Points: 65111 More actions · Answer 7

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.

dbwhz Old Hand Points: 328 More actions · Answer 8

Thanks alot David. You saved my day with that

A few small changes had to be done, but your suggestion is spot on.