Author |
Topic |
deepugun
Starting Member
11 Posts |
Posted - 2013-06-13 : 16:43:53
|
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.SoIf i run this query Select count(*) from ABC_View_AI would get 3129947 rowsBut 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 : 312996114 rows more than the ABC_View_ASelect 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 ILeft Outer join Test_ROLE CROn 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_IDAll 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 |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-13 : 18:03:04
|
You have CR.Address_ID in your group by clause,Is it possible that you may have multiple entries in test_code table with Client_id, Address_ID combinations?Verify this by running this query:[CODE]SELECT Client_ID, Address_ID FROM (SELECT ROW_NUMBER() OVER(PARTITION BY Client_ID, Address_ID Order by Client_ID, Address_ID) AS RN, Client_ID, Address_ID from test_code)A WHERE RN > 1[/CODE] |
|
|
deepugun
Starting Member
11 Posts |
Posted - 2013-06-13 : 20:23:59
|
Thanks for the reply.If i run the above query i get thousands of rows.Please let me know how i possibly resolve this issueThanks |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-13 : 20:52:20
|
What happens if you add MIN(CR.Address_ID) to your select statement? like this:[CODE]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), MIN(CR.Address_ID) from ABC_View_A ILeft Outer join Test_ROLE CROn 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[/CODE]Can you show your DDL & some example data? |
|
|
|
|
|