5 Replies Latest reply: Nov 13, 2012 4:41 AM by yoonas RSS

    duplicated across 2 tables

    user11969912
      hi all ,

      we have 2 tables TENANCY_CONTRACT and tenancy_contract_property the below select statement will give me all duplicated row across 2 tables


      select tc.contract_start_date, tc.Contract_end_date,tc.Tenant_id, tcAnnual_amount, tcp.ejari_property_id,COUNT(*) as cnt
      from TENANCY_CONTRACT tc
      JOIN tenancy_contract_property tcp
      ON (tc.tenancy_contract_id = tcp.tenancy_contract_id)
      group by contract_start_date,Contract_end_date,Tenant_id,Annual_amount,ejari_property_id
      having count > 1
      order by 6 desc

      here is some output

      CONTRACT_START_DATE     CONTRACT_END_DATE     TENANT_ID     ANNUAL_AMOUNT     EJARI_PROPERTY_ID     CNT

      1/1/2012     12/31/2012     3456      65178     78010     9
      1/1/2012     12/31/2012     11656     69200     56782     8
      1/1/2012     12/31/2012     11656     69200     56784     8
      1/1/2012     12/31/2012     11656      69200     56783     8
      3/12/2012     3/11/2013      70836     44000     119499     8

      how i can solve this issue please help