Argthtjtr

So a mobile telephony question. Have an adjacency table listing all the source and target adjacencies. So the target cell can be an external/border cell or a normal cell. So first I want to check if the target is a normal cell and if not I want to load the list of external cells from the external cell table.
So the tables are c_adjacent_cell_4g, the normal cell table is c_ecell and the target cell can be from the c_ecell table or from the c_externaleutrancellfdd table. Note the source cell will always be a normal cell and the target cell can be a normal cell or an external cell. So to my rather poor attempt at a select statement.

SELECT INT_ID, int_id 

FROM (

    SELECT adj.int_id, if(src.earfcnDl = tgt.earfcnDl, 'ADJS', 'ADJI') AS TYPE 

    FROM C_ADJACENT_CELL_4G adj 

    JOIN C_ECELL src ON (src.int_id = adj.src_cell_int_id) 

    JOIN C_ECELL tgt ON (tgt.int_id = adj.adj_cell_int_id) 

    LEFT JOIN C_EXTERNALEUTRANCELLFDD tgt ON (tgt.int_id = adj.adj_cell_int_id) 

    WHERE src.earfcndl IS NOT NULL AND tgt.earfcndl IS NOT NULL) DATA;

Is it possible I should be using a union statement rather than a left join. Thanks.

We could make use a UNION ALL set operator, to combine rows from C_ECELL and C_EXTERNALEUTRANCELLFDD into a single set, as an inline view, and then join to the combined set:

SELECT ...



  FROM C_ADJACENT_CELL_4G adj

  JOIN ( 

         SELECT 'i' AS src

              , intl.int_id

              , intl.earfcndl

           FROM C_ECELL int

          UNION ALL

         SELECT 'e' AS src

              , extl.int_id

              , extl.earfcndl

           FROM C_EXTERNALEUTRANCELLFDD extl

       ) tgt

    ON tgt.int_id = ...

If the same value of int_id appears in both intl and extl, the join operation will match both rows.

But I would avoid the UNION ALL approach. And instead do outer joins to both of the target tables. And then do a check (in an expression in the SELECT list) to determine if we found a match in C_ECELL. If not, we must have found a match in C_EXTERNALEUTRANCELLFDD.

Something along these lines:

SELECT adj.int_id     AS _adj__int_id

     , src.int_id     AS _src__int_id

     , tgt.int_id     AS _tgt__int_id

     , ext.int_id     AS _ext__int_id



     , src.earfcndl   AS _src__earfcndl

     , tgt.earfcndl   AS _tgt__earfcndl

     , ext.earfcndl   AS _ext__earfcndl



     , IF(adj.adj_cell_int_id = tgt.int_id, 'internal', 'external')



  FROM C_ADJACENT_CELL_4G adj



  JOIN C_ECELL src

    ON src.int_id = adj.src_cell_int_id



  LEFT

  JOIN C_ECELL tgt

    ON tgt.int_id = adj.adj_cell_int_id



  LEFT

  JOIN C_EXTERNALEUTRANCELLFDD ext

    ON ext.int_id = adj.adj_cell_int_id



 WHERE src.earfcndl IS NOT NULL

   AND ( tgt.earfcndl IS NOT NULL OR ext.earfcndl IS NOT NULL )

(The condition in the WHERE clause guarantees us that we found a matching row in either tgt or ext; if there wasn't a matching row in either, then earfcndl column from both outer joined tables would be NULL.)

You need to join with a UNION of C_ECELL and C_EXTERNALEUTRANCELLFDD.

SELECT adj.int_id, if(src.earfcnDl = tgt.earfcnDl, 'ADJS', 'ADJI') AS TYPE 

FROM C_ADJACENT_CELL_4G adj 

JOIN C_ECELL src ON (src.int_id = adj.src_cell_int_id) 

JOIN (

    SELECT int_id, earfcnDl

    FROM C_ECELL

    WHERE earfcndl IS NOT NULL

    UNION

    SELECT int_id, earfcnDl

    FROM C_EXTERNALEUTRANCELLFDD

    WHERE earfcndl IS NOT NULL) AS tgt ON (tgt.int_id = adj.adj_cell_int_id) 

WHERE src.earfcndl IS NOT NULL