Please help to build SQL SELECT request with GROUP BY

Question

I have following tables:

callrecord table
callid, calltypekey, requeuecount
123     1            -1
123     2            0
123     3            1
234     1            0
234     3            1
435     3            0
435     1            1
567     1            -1
678     1            0

calltypes table
calltypekey, calltypename
1            name1
2            name2
3            name3

Following SQL request works fine:

SELECT callid, min(requeuecount) as minreq, max(requeuecount) as maxreq
FROM callrecord 
GROUP BY callid
HAVING count(callid) > 1

and produce the following result:

callid, minreq, maxreq 
123     -1      1
234     0       1
435     0       1

I would like to add calltypekey value corresponding to min(requeuecount) record, i.e. result should be the following:

callid, minreq, maxreq, minCTK, maxCTK 
123     -1      1       1       3
234     0       1       1       3
435     0       1       3       1

or even with joined names:

callid, minreq, maxreq, minname, maxname 
123     -1      1       name1    name3
234     0       1       name1    name3
435     0       1       name3    name1

Is this possible?

Answer 1

Please give this a try.

select t1.callid, t1.minreq, t1.maxreq, t2.Name as minname, t3.Name as maxname from (
SELECT callid, min(requeuecount) as minreq, max(requeuecount) as maxreq
FROM callrecord 
GROUP BY callid
HAVING count(callid) > 1) as t1
left join 
    (select c.callid, ct.Name, c.requeuecount from callrecord c
        inner join calltypes ct on ct.calltypekey = c.calltypekey)  t2 on t2.callid = t1.callid and t2.requeuecount = t1.minreq
left join 
    (select c.callid, ct.Name, c.requeuecount from callrecord c
        inner join calltypes ct on ct.calltypekey = c.calltypekey)  t3 on t3.callid = t1.callid and t3.requeuecount = t1.maxreq