CODEWITHSUNDEEP
mysqlsql
Benjlaud1
Benjlaud1

Reputation: 15

How to query for rows that link to another row with a specific ID in MySQL?

I am trying to query two different tables (CALL_HISTORY and HUB_DIRECTORY) to find all the call records that are made between a 'hub store' and a 'spoke store'. Each call has a CallID field and an entry is made with the id of the store that initiated the call and then a separate entry is made for each store that receives the call, and these all have the id of the store that receives them. So they all have the same CallID but the stores id (DID) is different for each.

The problem is that not every call is between a hub and its spoke, so I need to filter it out to find only these records.

Sample Call Data

RecordID | CallID  | DID   | CallDirection | StartTime 
--------------------------------------------------------
1563486  | 255429  | 492   | Initiated     | 1520870539
1563487  | 255429  | 849   | Received      | 1520870539
1563484  | 255430  | 1098  | Initiated     | 1520870562
1563485  | 255430  | 1098  | Received      | 1520870562
1563482  | 255431  | 307   | Initiated     | 1520870567
1563483  | 255431  | 1013  | Received      | 1520870567
1563506  | 255432  | 1108  | Initiated     | 1520870580
1563509  | 255432  | 1108  | Received      | 1520870580

Here you see a sample of the calls, the CallID group highlighted is between a hub and its spoke and the rest are not. The hubs and spokes are linked together in the HUB_DIRECTORY like so:

HUB_DIRECTORY SAMPLE

HubStore | HubDID | SpokeStore | SpokeDID
-----------------------------------------
4        | 37     | Store0004  | 37
4        | 37     | Store0522  | 470
7        | 1083   | Store0007  | 1083
7        | 1083   | Store1000  | 714
7        | 1083   | Store1055  | 759
12       | 38     | Store0012  | 38
12       | 38     | Store1063  | 758
13       | 45     | Store0013  | 45
13       | 45     | Store0337  | 296
13       | 45     | Store1012  | 724

The HubDID and SpokeDID fields are the same as the DID in CALL_HISTORY. So I'm looking to query for calls where the initiated call DID exists in the HUB_DIRECTORY table, as either a HubDID or a SpokeDID, and its CallID also has a record with a DID that matches with the appropriate hub/spoke.

My end goal would look like this:

HUB        | Spoke      | Initiated | Received
-----------------------------------------------
Store.0004 | Store.0522 |   304     |   723

I believe I will need to use a UNION to get the row with the hub or spoke but I am just unable to wrap my head around how this would be done.

Upvotes: 1

Views: 170

Answers (2)

Nick
Nick

Reputation: 147176

I think this query will give you the results you want. It works on the limited sample data you provided.

select h1.hubstore, h1.hubdid,
       h1.spokestore, h1.spokedid,
       count(distinct if(c2.recordid is null or c1.did!=h1.hubdid, null, c1.recordid)) as initiated, 
       count(distinct if(c2.did!=h1.hubdid, null, c2.recordid)) as received     
from hub_directory h1 
left join (select * from call_history where calldirection='Initiated') c1 
    on c1.did=h1.hubdid or c1.did=h1.spokedid
left join (select * from call_history where calldirection='Received') c2 
    on c2.callid = c1.callid and c2.did=if(c1.did=h1.hubdid, h1.spokedid, h1.hubdid)
group by h1.hubstore, h1.spokestore

Based on the new sample data at your fiddle, this query gives

hubstore    spokestore  initiated   received
355         Store0355   0           0
355         Store0362   0           0
355         Store0655   0           0
357         Store0233   1           2
357         Store0357   0           0
360         Store0360   0           0
360         Store0868   0           0
360         Store1091   0           0
363         Store0363   0           0
363         Store1462   1           0
363         Store1507   1           0
363         Store2507   0           0

Upvotes: 1

Alpesh Jikadra
Alpesh Jikadra

Reputation: 1722

As of now What I understand is like, You want to get data like how many call Received By Hub and How many call Initiated by Hub.

select 
a.hubDID, a.CallDirection, count(a.CallDirection) , 
hb.SpokeDID, ch.CallDirection, count(ch.CallDirection)   
from CALL_HISTORY ch inner join 
(
    select RecordID, CallID, DID, CallDirection, StartTime, h.HubStore , h.hubDID
    from CALL_HISTORY c inner join HUB_DIRECTORY h on (c.DID = h.HubDID)
) 
as a 
on ch.CallID = a.CallID 
and a.RecordID <> ch.RecordID
inner join HUB_DIRECTORY hb on hb.SpokeDID = ch.DID

Try here Demo

Grouped Data Query : 

select 
a.hubDID, a.CallDirection, count(a.CallDirection) , 
count(hb.SpokeDID),
ch.CallDirection, count(ch.CallDirection)   
from CALL_HISTORY ch inner join 
(
    select distinct c.RecordID, c.CallID, c.DID, c.CallDirection, c.StartTime, c.DID  as hubDID
    from CALL_HISTORY c inner join HUB_DIRECTORY h on (c.DID = h.HubDID)
) 
as a 
on ch.CallID = a.CallID 
and a.RecordID <> ch.RecordID
inner join HUB_DIRECTORY hb on hb.SpokeDID = ch.DID
group by a.hubDID, a.CallDirection,

ch.CallDirection
;

Without Group Data Query:

select 
a.hubDID, a.CallDirection, count(a.CallDirection) , 
hb.SpokeDID,hb.SpokeDID,
ch.CallDirection, count(ch.CallDirection)   
from CALL_HISTORY ch inner join 
(
    select distinct c.RecordID, c.CallID, c.DID, c.CallDirection, c.StartTime, c.DID  as hubDID
    from CALL_HISTORY c inner join HUB_DIRECTORY h on (c.DID = h.HubDID)
) 
as a 
on ch.CallID = a.CallID 
and a.RecordID <> ch.RecordID
inner join HUB_DIRECTORY hb on hb.SpokeDID = ch.DID
group by a.hubDID, a.CallDirection,
hb.SpokeDID, 
ch.CallDirection
;

Upvotes: 0

Related Questions