How to select directed and non-directed links that contain a specific record reference

advertisements

There are two tables: M_LINK and M_LINK_DATA.

CREATE TABLE M_LINK (
    ID             INTEGER NOT NULL,
    ISDIRECTED     SMALLINT DEFAULT 0 NOT NULL CHECK (ISDIRECTED IN (0,1))
);

CREATE TABLE M_LINK_DATA (
    ID           INTEGER NOT NULL,
    LINKREF      INTEGER NOT NULL,            -- reference to any record of table M_LINK
    MAINRECREF   INTEGER DEFAULT -1 NOT NULL, -- reference to any record of some third table
    SUBRECREF    INTEGER DEFAULT -1 NOT NULL  -- reference to any record of some third table
);

I want to select all SUBRECREF of M_LINK_DATA where LINKREF is a specific record :linkref of M_LINK and MAINRECREF is a specific record :recref of some third table. So far, so good.

This is my SQL statement to do this:

select SUBRECREF
  from m_link_data
  where LINKREF = :linkref and
        MAINRECREF = :recref

Furthermore, if ISDIRECTED of record :linkref from M_LINK equals 0, the result should also contain every MAINRECREF of M_LINK_DATA where SUBRECREF is the same specific record :recref of known third table.

sample data

-----M_LINK----
ID | ISDIRECTED
---+-----------
 1 |          1
 2 |          0

-------------M_LINK_DATA-------------
ID | LINKREF | MAINRECREF | SUBRECREF
---+---------+------------+----------
 3 |       1 |         10 |        11
 4 |       1 |         10 |        12
 5 |       1 |         13 |        10
 6 |       2 |         10 |        11
 7 |       2 |         14 |        11
 8 |       2 |         13 |        10
 9 |       2 |         15 |        10

desired output

for :linkref = 1 and :recref = 10:

RECREF
------
    11
    12

for :linkref = 2 and :recref = 10:

RECREF
------
    11
    13
    15

As you can see, there should be only one result column, containing either MAINRECREF or SUBRECREF.

Please help me extending my SQL query to consider M_LINK.ISDIRECTED.


You can do this with a single query without resorting to stored procedures by using a union between a query for the main direction and one for the reverse direction.

with directed_links as (
    select ld.linkref, ld.mainrecref as origin, ld.subrecref as target
    from m_link_data ld
    union all
    select ld.linkref, ld.subrecref as origin, ld.mainrecref as target
    from m_link_data ld
    inner join m_link l on l.id = ld.linkref
    where l.isdirected = 0
)
select target
from directed_links
where linkref = :linkref and origin = :recref