none
One to many join question

    Question


  • Hello All,
    I am trying to do a join between a view and a table. It is a one to many join i.e Client record in ABC_VIEW_A will join with one or many records in the test_code table .But as i want only one one -one join , I decided to use the group by and min functionality to retreive only one record from the test_code table.So
    If i run this query 
    Select count(*) from ABC_View_A
    I would get 3129947 rows
    But if i run the below join query i get more number of rows than the number of rows in ABC_View_A(3129947) i.e. precisely : 3129961
    14 rows more than the ABC_View_A

    Select i.Client_Ref, i.Source, i.First_Name, i.Middle_Name, i.last_name, i.Address, i.City, i.State_Code, i.Zip_Code, i.Phone_Num, i.SSN, i.Gender, i.Marital_Status, i.DOB, i.Last_Update_Date, i.Last_Update_User, i.Time_Stamp, i.Client_ID,MIN(CR.test_CODE),CR.Address_ID 
    from ABC_View_A I
    Left Outer join 
    Test_ROLE CR
    On 
    I.Client_ID = CR.Client_ID 
    Group By 
    i.Client_Ref, i.Source, i.First_Name, i.Middle_Name, i.last_name, i.Address, i.City, i.State_Code, i.Zip_Code, i.Phone_Num, i.SSN, i.Gender, i.Marital_Status, i.DOB, i.Last_Update_Date, i.Last_Update_User, i.Time_Stamp, i.Client_ID,CR.Address_ID


    All the rows in the Test_code have Client_ID.
    Some of the rows in the ABC_View_A have the client_id and some does not have.But as i am doing a join on Client_ID and the client in test_code is always there, I do not see null as a issue in my join.
    It would be helpful if some one could help me in debugging this issue.
    Thanks

    • Edited by deepugun Thursday, June 13, 2013 11:19 PM
    Thursday, June 13, 2013 11:19 PM

All replies

  • Please post DDL.

    http://sqldevelop.wordpress.com/

    Thursday, June 13, 2013 11:28 PM
  • Try this

    ;WITH CTE_Test_Role
    (
    	SELECT Client_ID, MIN(test_CODE) test_CODE,Address_ID FROM Test_ROLE 
    		GROUP BY Client_ID, Address_ID
    )
    
    Select i.Client_Ref, i.Source, i.First_Name, i.Middle_Name, i.last_name, 
    i.Address, i.City, i.State_Code, i.Zip_Code, i.Phone_Num, i.SSN, i.Gender, 
    i.Marital_Status, i.DOB, i.Last_Update_Date, i.Last_Update_User, i.Time_Stamp, 
    i.Client_ID, CR.test_CODE,CR.Address_ID 
    
    FROM ABC_View_A I
    INNER JOIN CTE_Test_Role CR
    	ON I.Client_ID = CR.Client_ID
    Note: If the client has two different address ids in Test_ROLE it will fall into same problem you are facing currently. In that case you need to decide which record to keep considering address_id. So, modify CTE's inner query accordingly.

    Friday, June 14, 2013 12:19 AM
  • maybe test_role table have more than one client_id with different address_id ?
     can verify with this ?
    select client_id,address_id,count(1)
    from testrole
    group by client_id,address_id
    having count(1) >1

     
      can verify with this ?
     
      Select I.Client_ID ,i.Client_Ref, i.Source, i.First_Name, i.Middle_Name, i.last_name, i.Address,
    i.City, i.State_Code, i.Zip_Code, i.Phone_Num, i.SSN, i.Gender, i.Marital_Status, i.DOB, i.Last_Update_Date,
    i.Last_Update_User, i.Time_Stamp, i.Client_ID,MIN(CR.test_CODE),CR.Address_ID
    into #temp from ABC_View_A I
    Left Outer join
    Test_ROLE CR
    On
    I.Client_ID = CR.Client_ID
    Group By
    I.Client_ID,i.Client_Ref, i.Source, i.First_Name, i.Middle_Name, i.last_name, i.Address, i.City, i.State_Code,
     i.Zip_Code, i.Phone_Num, i.SSN, i.Gender, i.Marital_Status, i.DOB, i.Last_Update_Date,
      i.Last_Update_User, i.Time_Stamp, i.Client_ID,CR.Address_ID
     
     
      --get duplication client_id
       select client_id,COUNT(1)
      from #temp
      group by client_id
      having COUNT(1) >1
     
      --why it duplicate? 
      select * from #temp where client_id in(
      select client_id,COUNT(1)
      from #temp
      group by client_id
      having COUNT(1) >1)
      order by client_id

    Friday, June 14, 2013 2:57 AM