SQL Query Help

This is probably an easy one, I just can't remeber how to properly join these tables based on the data thats distinct.

I need:
Name, startTime, endTime, talkTime

tables:

QDetail
--------
sessionID (needs to be distinct) where disposition = 2 (points to CDetail)
targetID (points to skillGroupID)

ServiceQueue
-----------------
Name
skillGroupID (points to targetID)

CDetail
-----------
sessionID (points to QDetail)
startTime
endTime
talkTime
resourceID (WHERE resourceID = some var))
MrBaseball9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

csupaCommented:
Try this:

SELECT Name, startTime, endTime, talkTime FROM QDetail, ServiceQueue, CDetail WHERE
Qdetail.sessionID=CDetail.sessionID AND QDetail.targetID=ServiceQueue.skillGroupID WHERE QDetail.sessionID=2
0
dougaugCommented:
Try this:

select distinct sq.Name, cd.startTime, cd.endTime, cd.talktime
  from ServiceQueue sq INNER JOIN QDetail qd on (sq.skillGroupID = qd.targetID)
                       INNER JOIN CDetail cd on (qd.sessionID = cd.sessionID)
where qd.disposition = 2
  and cd.resourceID = @some_var
0
MrBaseball9Author Commented:
I actually have to throw you guys a curveball.... I was wrong on one page.

instead of WHERE disposition = 2 I need to get a (disinct sessionID), targetID, where MAX(disposition)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MrBaseball9Author Commented:
What happens in the QDetail column is that there could be duplicate sessionID's
The disposition could either be a 5 or a 2.
Most will be 2's but if one is set as a 5... I need take teh target ID associated with that, else I take the targetID  related to the 2
0
MrBaseball9Author Commented:
This query pulls everything but isn't using the proper disposition to get the targetID and CSQName

SELECT  AgentConnectionDetail.sessionID, AgentConnectionDetail.startDateTime, AgentConnectionDetail.endDateTime, AgentConnectionDetail.talkTime, ContactServiceQueue.skillGroupID, ContactQueueDetail.targetID, ContactServiceQueue.CSQName
FROM AgentConnectionDetail INNER JOIN ContactQueueDetail ON AgentConnectionDetail.sessionID = ContactQueueDetail.sessionID INNER JOIN ContactServiceQueue ON ContactQueueDetail.targetID = ContactServiceQueue.skillGroupID
WHERE     (AgentConnectionDetail.startDateTime > CONVERT(datetime, CONVERT(varchar, GETDATE(), 103), 103)) AND (AgentConnectionDetail.resourceID = 697)
ORDER BY AgentConnectionDetail.startDateTime
0
MrBaseball9Author Commented:
Got this for Disposition.. now to piece it all together:

SELECT     sessionID, targetID, disposition
FROM         ContactQueueDetail x
WHERE     (disposition =
                          (SELECT     MAX(disposition)
                            FROM          ContactQueueDetail
                            WHERE      sessionID = x.sessionID))
0
MrBaseball9Author Commented:
A little Farther.... I was able to get 2 of the 3 tables together. I now have 2 queries:
////////////Combines ContactServiceQueue and ContactQueueDetail////////////////////////////
SELECT     x.sessionID, x.targetID, x.disposition, ContactServiceQueue.skillGroupID, ContactServiceQueue.CSQName
FROM         ContactQueueDetail x INNER JOIN
                      ContactServiceQueue ON x.targetID = ContactServiceQueue.skillGroupID
WHERE     (x.sessionID > 24000164425) AND (x.disposition =
                          (SELECT     MAX(disposition)
                            FROM          ContactQueueDetail
                            WHERE      sessionID = x.sessionID))
ORDER BY x.sessionID
0
MrBaseball9Author Commented:
I think I got it!!!!
Does it make sense? The data looks correct, but not 100% sure.
/////////////////////////////

SELECT     y.startDateTime, y.endDateTime, y.talkTime, x.sessionID, x.targetID, x.disposition, ContactServiceQueue.skillGroupID,
                      ContactServiceQueue.CSQName
FROM         AgentConnectionDetail y INNER JOIN
                      ContactQueueDetail x INNER JOIN
                      ContactServiceQueue ON x.targetID = ContactServiceQueue.skillGroupID ON y.sessionID = x.sessionID
WHERE     (x.sessionID > 24000164425) AND (x.disposition =
                          (SELECT     MAX(disposition)
                            FROM          ContactQueueDetail
                            WHERE      sessionID = x.sessionID)) AND (y.resourceID = 697)
ORDER BY y.startDateTime
0
dougaugCommented:
In my opinion your last query is correct.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MrBaseball9Author Commented:
Thanks for pointing me in the right direction. I saw how you did the inne joins and then ran from there.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.