|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
November 15th, 2005, 08:22 PM
|
|||
|
|||
|
Complex query?
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?
|
|
#2
November 16th, 2005, 04:29 AM
|
|||
|
|||
|
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
|
|
| Viewing: Dev Articles Community Forums > Databases > General SQL Development > Complex query? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
