SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 One to many Join question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deepugun
Starting Member

11 Posts

Posted - 06/13/2013 :  16:43:53  Show Profile  Reply with Quote
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 on 06/13/2013 19:13:13

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/13/2013 :  18:03:04  Show Profile  Reply with Quote
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:

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

Edited by - MuMu88 on 06/13/2013 19:23:48
Go to Top of Page

deepugun
Starting Member

11 Posts

Posted - 06/13/2013 :  20:23:59  Show Profile  Reply with Quote
Thanks for the reply.If i run the above query i get thousands of rows.Please let me know how i possibly resolve this issue
Thanks

Edited by - deepugun on 06/13/2013 20:24:23
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/13/2013 :  20:52:20  Show Profile  Reply with Quote
What happens if you add MIN(CR.Address_ID) to your select statement? like this:

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 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



Can you show your DDL & some example data?

Edited by - MuMu88 on 06/13/2013 21:15:52
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000