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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
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
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)
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

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
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
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))
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
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
dougaugCommented:
In my opinion your last query is correct.

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