current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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
share|improve this question
    
How do you know that host2 is the destination host for host1? I don't see how you can make that connection. –  a_horse_with_no_name Aug 18 '14 at 14:31
    
The "bkvol1_DR" volume exists only on host "host2", and is the target of "vol1" because the name "vol1" appears in the volume string. –  John Aug 18 '14 at 14:42

1 Answer 1

up vote 3 down vote 
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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.