PostgreSQL SQL syntax – JOIN and substrings

join;postgresqlpostgresql-9.2select

I have a single table of hostnames and NFS exports, some but not all of which are related.
Example entries:

    HOST   VOLUME     
    -----  ---------- 
    host1  vol1
    host1  vol2
    host1  vol3
    host2  bkvol1_DR
    host2  bkvol2_DR
    host2  bkvol3_DR

I'm trying to create a query which will give me the following:

   SRCHOST  DSTHOST  SRCVOL    DSTVOL
   -------  -------  ------    ------
   host1    host2    vol1      bkvol1_DR
   host1    host2    vol2      bkvol2_DR
   host1    host2    vol3      bkvol3_DR

I've tried various incantations of substring(), trim(), and join clauses, and I'm getting nowhere. This is on PostgreSQL 9.2. In non-functional code, this is what I want:

select a.host, b.host, a.volume, b.volume from table as a, table as b 
    where a.volume is a substring of b.volume

Best Answer

select a.host, b.host, b.volume
from hosts as a
  join hosts as b 
    on b.volume like '%'||a.volume||'%' 
   and a.host <> b.host;

Instead of LIKE you can also use position which might be easier to read:

select a.host, b.host, b.volume
from hosts as a
  join hosts as b 
    on a.host <> b.host
   and position(a.volume in b.volume) > 0

SQLFiddle: http://sqlfiddle.com/#!15/a67c5/1

Related Question