One table with two kinds of call records, with different ID in "task". Related calls always have same ID in "callID". I want to get some fields from record with task=111 and others from task=333, for each that pair. Like that(pseudocode):

SELECT A.user, B.phone, A.datetime, A.duration, B.status, A.callID
FROM MyTable
WHERE A.datetime BETWEEN... AND B.user='Alice'

JOIN? MERGE? UNION? Temporary tables and custom procedure?

CodePudding user response：

You can join on the same table using Aliases (as if they were different tables) and supply additional WHERE/ON clauses

SELECT A.user, B.phone, A.datetime, A.duration, B.status, A.callID
FROM MyTable A
JOIN MyTable B ON B.CallId = A.CallId AND B.TaskId = 333
WHERE A.datetime BETWEEN... AND B.user='Alice'
  AND A.TaskId = 222

CodePudding user response：

You may aggregate by the callID and then use conditional aggregation to pivot out the fields of interest. For example, to report the phones you could try:

SELECT callID,
       MAX(CASE WHEN task = 111 THEN phone END) AS phone_111,
       MAX(CASE WHEN task = 333 THEN phone END) AS phone_333
FROM yourTable
GROUP BY callID;

You may select other fields per task as shown above.