One to many Join question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

One to many Join question

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

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

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

[/CODE]
Can you show your DDL & some example data?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources