title: How to judge exactly the same in the child table
body: <p>In my Oracle DB, I have 2 tables <code>d</code> and <code>w</code>:  </p>
  
  <p><code>table d</code>  </p>
  
  <pre><code>    INVOICECODE    VARCHAR2(32 BYTE)     PRIMARY KEY  
      COMBINATIONNAME    NVARCHAR2(100 CHAR)  
      STARTTIME    DATE  
      ENDTIME    DATE  
      STATUS    NUMBER(10,0)  
      .....  
  </code></pre>
  
  <p><code>table w</code>  </p>
  
  <pre><code>SERIALID  VARCHAR2(32 BYTE)  PRIMARY KEY  
  INVOICECODE  VARCHAR2(32 BYTE)  
  WARECODE  VARCHAR2(20 BYTE)  
  WARENAME  NVARCHAR2(100 CHAR)  
  </code></pre>
  
  <p>....  </p>
  
  <p>When I add or edit data on the WEB UI, I must to verify that <code>STATUS</code> time does not overlap and  sub-table data can not be exactly the same.</p>
  
  <p>e.g. the new data is  </p>
  
  <pre><code>StartTime: 2017/07/25  
  EndTime: 2017/07/28  
  
  
  WareCode   WareName  
  123        A   
  222        B  
  </code></pre>
  
  <p>if there are two rows date in the <code>w</code> table(WareCode: 123，222) ，meet the conditions, can't add the new data;<br>
  if WareCode: 123  or WareCode: 123，222, 456， can add the new data. how to write the sql:</p>
  
  <pre><code>select count(1) from d 
  where status = 1 
  and (StartTime BETWEEN '2017-07-25' AND '2017-07-28' 
       OR EndTime BETWEEN '2017-07-25' AND '2017-07-28' 
       OR '2017-07-25' Between StartTime AND EndTime 
       OR '2017-07-28' BETWEEN StartTime AND EndTime) 
  and (/*....*/)
  </code></pre>
  
  <p>the sample data<br>
  table d  :  </p>
  
  <pre><code>|----------------------------------------------------------------------------------|
  | INVOICECODE | COMBINATIONNAME   | STARTTIME        | ENDTIME          |  STATUS  | 
  |----------------------------------------------------------------------------------|
  | 1101        | GoodsCombination1 | 2017-07-10 00:01 | 2017-07-20 15:00 |  0       |
  |----------------------------------------------------------------------------------|
  | 1105        | GoodsCombination2 | 2017-07-24 15:00 | 2017-07-26 15:00 |  1       |
  -----------------------------------------------------------------------------------|
  | 210         | GoodsCombination3 | 2017-07-26 12:00 | 2017-07-27 15:00 |  1       |
  -----------------------------------------------------------------------------------|
  | 88          | GoodsCombination4 |  017-07-25 09:00 | 2017-07-26 09:00 |  1       |
  ------------------------------------------------------------------------------------
  </code></pre>
  
  <p>The STATUS column can be 0, 1, 2(Not audited、Audited、Rejected)<br>
  table w uses INVOICECODE association table d  </p>
  
  <p>table w  :  </p>
  
  <pre><code>|----------------------------------------------|
  | SERIALID | INVOICECODE | WARECODE | WARENAME |
  |----------------------------------------------|
  | 1        |   1101      |   123    |  A       |
  |----------------------------------------------|
  | 2        |   1105      |   123    |  A       |
  -----------------------------------------------|
  | 3        |   1105      |   222    |  B       |
  -----------------------------------------------|
  | 4        |   1105      |   300    |  D       |
  -----------------------------------------------|
  | 5        |   210       |   123    |  A       |
  -----------------------------------------------|
  | 6        |   210       |   222    |  B       |
  -----------------------------------------------|
  | 7        |   88        |   123    |  A       |
  -----------------------------------------------|
  | 8        |   88        |   300    |  D       |
  ------------------------------------------------
  </code></pre>
  
  <p>When new data like this, can insert to db:    </p>
  
  <pre><code>CombinationName: aaaaa  
  StartTime: 2017-07-20 00:00:00  
  EndTime: 2017-07-23 00:00:00  
  
  WareCode   WareName  
  222        B   
  300        D  
  </code></pre>
  
  <p>Like this, can't insert db because 210' status=1 and there are two identical data in table d:    </p>
  
  <pre><code>CombinationName: aaaaa  
  StartTime: 2017-07-20 00:00:00  
  EndTime: 2017-07-23 00:00:00  
  
  WareCode   WareName    
  123        A     
  222        B 
  </code></pre>
  
  <p>Can't insert db because status=1 and time overlap of 1105, 210, 88 in table d:    </p>
  
  <pre><code>CombinationName: aaaaa  
  StartTime: 2017-07-25 00:00:00  
  EndTime: 2017-07-27 00:00:00  
  
  WareCode   WareName  
  123        A   
  222        B 
  44         F 
  </code></pre>
  
  <hr>
  
  <p>Maybe sort the new data for WareCode, and then use the below SQL :   </p>
  
  <pre><code>Select Count(1) From (
    Select (
        Select
             listagg(WareCode, ';') within group (Order by WareCode) as mergeWareCode
      From w
      Where InvoiceCode = d.InvoiceCode
      Group by InvoiceCode
    ) as mergeWareCode, d.InvoiceCode From d
    Where d.Status = 1 and ( StartTime Between to_date('2017-07-25','yyyy-mm-dd hh24:mi:ss') and to_date('2017-07-28','yyyy-mm-dd hh24:mi:ss')
      or EndTime Between to_date('2017-07-25','yyyy-mm-dd hh24:mi:ss') and to_date('2017-07-28','yyyy-mm-dd hh24:mi:ss')
      or to_date('2017-07-25','yyyy-mm-dd hh24:mi:ss') Between StartTime and EndTime
      or to_date('2017-07-28','yyyy-mm-dd hh24:mi:ss') Between StartTime and EndTime ) 
  ) Where mergeWareCode='123;222'
  ;
  </code></pre>