Query in MySQL when there are two ids

Question

Query in MySQL when there are two ids

Navigation

#1 by (2 votes)

1

I have a table in MySQL that writes connection information from an IP PBX. It always stores the connection with a uniqueid itself.

But I noticed that the time in the URA is also stored in the same table with the same uniqueid .

So when someone calls, it falls into the URA and is answered by the person, two entries are generated in the database for the same call.

My problem is this: I need in query , when I give a select * from tabela; , only display data from the second entry except for the callduration field, which has to add the value of the two entries.

I've been looking at google and I have no idea how to do a query in these patterns.

Here is a print of an example in the table:

mysql sql

asked by anonymous 27.11.2017 / 16:39

1 answer

Form submitting null values Error in query INSERT INTO

score 2 · Accepted Answer

Assuming that:

All records are duplicates.
Duplicates always form a pair having a "Dial" and a "Hangup" in column lastapp with same uniqueid .
All elements of the Hangup that are filled in are the same as in the "Dial" (with the exception of lastapp ).

So this should work:

SELECT
    a.calldate AS calldate,
    a.clid, AS clid,
    a.src AS src,
    a.dst AS dst,
    a.dcontext AS dcontext,
    a.channel AS channel,
    a.dstchannel AS dstchannel,
    'Dial + Hangup' AS lastapp,
    a.lastdata AS lastdata,
    (a.duration + b.duration) AS duration,
    (a.billsec + b.billsec) AS billsec,
    a.disposition AS disposition,
    a.amaflags AS amaflags,
    a.accountcode AS accountcode,
    a.uniqueid AS uniqueid
FROM
    tabela a
INNER JOIN
    tabela b ON a.uniqueid = b.uniqueid
WHERE a.lastapp = 'Dial'
AND b.lastapp = 'Hangup'

If there are records that are not duplicates too and you want to display them also along with the duplicate pair (which should be considered as a single record), try this:

SELECT
    a.calldate AS calldate,
    a.clid, AS clid,
    a.src AS src,
    a.dst AS dst,
    a.dcontext AS dcontext,
    a.channel AS channel,
    a.dstchannel AS dstchannel,
    IF(ISNULL(b.uniqueid), a.lastapp, 'Dial + Hangup') AS lastapp,
    a.lastdata AS lastdata,
    (a.duration + COALESCE(b.duration, 0)) AS duration,
    (a.billsec + COALESCE(b.billsec, 0)) AS billsec,
    a.disposition AS disposition,
    a.amaflags AS amaflags,
    a.accountcode AS accountcode,
    a.uniqueid AS uniqueid
FROM
    tabela a
LEFT OUTER JOIN
    tabela b ON a.uniqueid = b.uniqueid
WHERE a.lastapp = 'Dial'
AND (ISNULL(b.uniqueid) OR b.lastapp = 'Hangup')