Need help with the join gettign too many rows

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with the join gettign too many rows

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-02-14 : 09:44:19

Hi,

I am using sql 2005.

I have the requirement that I need to get the data for ids for only those users that have access to the ids when a Group is specified in teh front end.

I have the below parameter passed to the stored procedure

@UserID smallint
@CustText1 nvarchar(100)
@CustBool1 bit = 0

The below are local variables

Declare @Group nvarchar(100)
Declare @UseLatestPeriod bit

Set @Group =
CASE
WHEN LEN(RTRIM(IsNull(@CustText1,''))) < 1 THEN NULL
ELSE RTRIM(@CustText1)
END

Set @UseLatestPeriod = @CustBool1

DECLARE @SelectedFunds TABLE(pshipid nvarchar(100))

IF @Group IS NULL
Begin
insert into @SelectedFunds

SELECT DISTINCT pshipid from SelectedCapitalAccounts Where SessionID = @SessionID AND ReportID = @ReportID AND Selected = 1
end
ELSE
Begin
insert into @SelectedFunds
SELECT DISTINCT pshipid FROM GroupPshipAccess GPA
INNER JOIN UserGroup UG ON GPA.UserGroupID = UG.UserGroupID
INNER JOIN UserGroupMember UGM ON GPA.UserGroupID = UGM.UserGroupID
WHERE UserGroupName = @Group
group by pshipid

I have three tables UserGroup, UserGroupMember and GrouPshipAccess.

If a Group is specified, include ids that are in the group requested. Table GroupPshipAccess identifies id in a group.

If a Group is specified, only include ids that a user has access to. Table UserGroupMember contains user's group access.

I am tryign to account for th parameter @UserID passed in the stored procedure and I am not sure how to fit in the UserID with the above joins in the insert to the table.

I tried something like the below but I get too many rows(270K)

DECLARE @UserID smallint
DECLARE @SelectedFunds TABLE(pshipid nvarchar(100), UserID smallint)
insert into @SelectedFunds
SELECT DISTINCT GPA.pshipid, UGM.UserID FROM GroupPshipAccess GPA
INNER JOIN UserGroup UG ON GPA.UserGroupID = UG.UserGroupID
INNER JOIN UserGroupMember UGM ON UG.UserGroupID = UGM.UserGroupID

The query below gives me 1804 rows which is correct. How can I account for user's group access and set the paramter @UserID?

SELECT DISTINCT pshipid FROM GroupPshipAccess GPA
INNER JOIN UserGroup UG ON GPA.UserGroupID = UG.UserGroupID
INNER JOIN UserGroupMember UGM ON GPA.UserGroupID = UGM.UserGroupID
WHERE UserGroupName = @Group
group by pshipid

Thanks for your help.

Regards,
sqlnovice123

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-02-14 : 10:00:32

I tried the below:

DECLARE @SelectedFunds TABLE(pshipid nvarchar(100), UserGroupID smallint, UserID smallint)
insert into @SelectedFunds

SELECT DISTINCT GPA.pshipid, UserGroupID, UserID FROM GroupPshipAccess GPA
INNER JOIN UserGroup UG ON GPA.UserGroupID = UG.UserGroupID
INNER JOIN UserGroupMember UGM ON UG.UserGroupID = UGM.UserGroupID

WHERE UserGroupName = 'BLUECREST'
group by GPA.pshipid, GPA.UserGroupID, UGM.UserID
select * from @SelectedFunds

I get 1128 rows. example

pshipid UserGroupID UserID
42 12 61
42 164 61
42 179 61
42 321 61
42 336 61
42 403 61
42 687 61
42 691 61
42 692 61
42 733 61
42 734 61
42 742 61
42 502 61
42 509 61
42 586 61
42 596 61
42 623 61
42 624 61
42 415 61
42 425 61
42 459 61
42 476 61
42 490 61
42 493 61
660 12 61
660 164 61
660 179 61
660 321 61
660 336 61
660 403 61
660 687 61
660 691 61
660 692 61
660 733 61
660 734 61
660 742 61
660 502 61
660 509 61
660 586 61
660 596 61
660 623 61
660 624 61
660 415 61
660 425 61
660 459 61
660 476 61
660 490 61
660 493 61
661 12 61
661 164 61
661 179 61
661 321 61
661 336 61
661 403 61
661 687 61
661 691 61
661 692 61
661 733 61
661 734 61
661 742 61
661 502 61
661 509 61
661 586 61
661 596 61
661 623 61
661 624 61
661 415 61
661 425 61
661 459 61
661 476 61
661 490 61
661 493 61
743 12 61
743 164 61
743 179 61
743 321 61
743 336 61
743 403 61
743 687 61
743 691 61
743 692 61
743 733 61
743 734 61
743 742 61
743 502 61
743 509 61
743 586 61
743 596 61
743 623 61
743 624 61
743 415 61
743 425 61
743 459 61
743 476 61
743 490 61
743 493 61
744 12 61
744 164 61
744 179 61
744 321 61
744 336 61
744 403 61
744 687 61
744 691 61
744 692 61
744 733 61
744 734 61
744 742 61
744 502 61
744 509 61
744 586 61
744 596 61
744 623 61
744 624 61
744 415 61
744 425 61
744 459 61
744 476 61
744 490 61
744 493 61
750 12 61
750 164 61
750 179 61
750 321 61
750 336 61
750 403 61
750 687 61
750 691 61
750 692 61
750 733 61
750 734 61
750 742 61
750 502 61
750 509 61
750 586 61
750 596 61
750 623 61
750 624 61
750 415 61
750 425 61
750 459 61
750 476 61
750 490 61
750 493 61
751 12 61
751 164 61
751 179 61
751 321 61
751 336 61
751 403 61
751 687 61
751 691 61
751 692 61
751 733 61
751 734 61
751 742 61
751 502 61
751 509 61
751 586 61
751 596 61
751 623 61
751 624 61
751 415 61
751 425 61
751 459 61
751 476 61
751 490 61
751 493 61
752 12 61
752 164 61
752 179 61
752 321 61
752 336 61
752 403 61
752 687 61
752 691 61
752 692 61
752 733 61
752 734 61
752 742 61
752 502 61
752 509 61
752 586 61
752 596 61
752 623 61
752 624 61
752 415 61
752 425 61
752 459 61
752 476 61
752 490 61
752 493 61
753 12 61
753 164 61
753 179 61
753 321 61
753 336 61
753 403 61
753 687 61
753 691 61
753 692 61
753 733 61
753 734 61
753 742 61
753 502 61
753 509 61
753 586 61
753 596 61
753 623 61
753 624 61
753 415 61
753 425 61
753 459 61
753 476 61
753 490 61
753 493 61
760 12 61
760 164 61
760 179 61
760 321 61
760 336 61
760 403 61
760 687 61
760 691 61
760 692 61
760 733 61
760 734 61
760 742 61
760 502 61
760 509 61
760 586 61
760 596 61
760 623 61
760 624 61
760 415 61
760 425 61
760 459 61
760 476 61
760 490 61
760 493 61
775 12 61
775 164 61
775 179 61
775 321 61
775 336 61
775 403 61
775 687 61
775 691 61
775 692 61
775 733 61
775 734 61
775 742 61
775 502 61
775 509 61
775 586 61
775 596 61
775 623 61
775 624 61
775 415 61
775 425 61
775 459 61
775 476 61
775 490 61
775 493 61
776 12 61
776 164 61
776 179 61
776 321 61
776 336 61
776 403 61
776 687 61
776 691 61
776 692 61
776 733 61
776 734 61
776 742 61
776 502 61
776 509 61
776 586 61
776 596 61
776 623 61
776 624 61
776 415 61
776 425 61
776 459 61
776 476 61
776 490 61
776 493 61
777 12 61
777 164 61
777 179 61
777 321 61
777 336 61
777 403 61
777 687 61
777 691 61
777 692 61
777 733 61
777 734 61
777 742 61
777 502 61
777 509 61
777 586 61
777 596 61
777 623 61
777 624 61
777 415 61
777 425 61
777 459 61
777 476 61
777 490 61
777 493 61
781 12 61
781 164 61
781 179 61
781 321 61
781 336 61
781 403 61
781 687 61
781 691 61
781 692 61
781 733 61
781 734 61
781 742 61
781 502 61
781 509 61
781 586 61
781 596 61
781 623 61
781 624 61
781 415 61
781 425 61
781 459 61
781 476 61
781 490 61
781 493 61
788 12 61
788 164 61
788 179 61
788 321 61
788 336 61
788 403 61
788 687 61
788 691 61
788 692 61
788 733 61
788 734 61
788 742 61
788 502 61
788 509 61
788 586 61
788 596 61
788 623 61
788 624 61
788 415 61
788 425 61
788 459 61
788 476 61
788 490 61
788 493 61
793 12 61
793 164 61
793 179 61
793 321 61
793 336 61
793 403 61
793 687 61
793 691 61
793 692 61
793 733 61
793 734 61
793 742 61
793 502 61
793 509 61
793 586 61
793 596 61
793 623 61
793 624 61
793 415 61
793 425 61
793 459 61
793 476 61
793 490 61
793 493 61
794 12 61
794 164 61
794 179 61
794 321 61
794 336 61
794 403 61
794 687 61
794 691 61
794 692 61
794 733 61
794 734 61
794 742 61
794 502 61
794 509 61
794 586 61
794 596 61
794 623 61
794 624 61
794 415 61
794 425 61
794 459 61
794 476 61
794 490 61
794 493 61
797 12 61
797 164 61
797 179 61
797 321 61
797 336 61
797 403 61
797 687 61
797 691 61
797 692 61
797 733 61
797 734 61
797 742 61
797 502 61
797 509 61
797 586 61
797 596 61
797 623 61
797 624 61
797 415 61
797 425 61
797 459 61
797 476 61
797 490 61
797 493 61
798 12 61
798 164 61
798 179 61
798 321 61
798 336 61
798 403 61
798 687 61
798 691 61
798 692 61
798 733 61
798 734 61
798 742 61
798 502 61
798 509 61
798 586 61
798 596 61
798 623 61
798 624 61
798 415 61
798 425 61
798 459 61
798 476 61
798 490 61
798 493 61
799 12 61
799 164 61
799 179 61
799 321 61
799 336 61
799 403 61
799 687 61
799 691 61
799 692 61
799 733 61
799 734 61
799 742 61
799 502 61
799 509 61
799 586 61
799 596 61
799 623 61
799 624 61
799 415 61
799 425 61
799 459 61
799 476 61
799 490 61
799 493 61
807 12 61
807 164 61
807 179 61
807 321 61
807 336 61
807 403 61
807 687 61
807 691 61
807 692 61
807 733 61
807 734 61
807 742 61
807 502 61
807 509 61
807 586 61
807 596 61
807 623 61
807 624 61
807 415 61
807 425 61
807 459 61
807 476 61
807 490 61
807 493 61
808 12 61
808 164 61
808 179 61
808 321 61
808 336 61
808 403 61
808 687 61
808 691 61
808 692 61
808 733 61
808 734 61
808 742 61
808 502 61
808 509 61
808 586 61
808 596 61
808 623 61
808 624 61
808 415 61
808 425 61
808 459 61
808 476 61
808 490 61
808 493 61
813 12 61
813 164 61
813 179 61
813 321 61
813 336 61
813 403 61
813 687 61
813 691 61
813 692 61
813 733 61
813 734 61
813 742 61
813 502 61
813 509 61
813 586 61
813 596 61
813 623 61
813 624 61
813 415 61
813 425 61
813 459 61
813 476 61
813 490 61
813 493 61
814 12 61
814 164 61
814 179 61
814 321 61
814 336 61
814 403 61
814 687 61
814 691 61
814 692 61
814 733 61
814 734 61
814 742 61
814 502 61
814 509 61
814 586 61
814 596 61
814 623 61
814 624 61
814 415 61
814 425 61
814 459 61
814 476 61
814 490 61
814 493 61
822 12 61
822 164 61
822 179 61
822 321 61
822 336 61
822 403 61
822 687 61
822 691 61
822 692 61
822 733 61
822 734 61
822 742 61
822 502 61
822 509 61
822 586 61
822 596 61
822 623 61
822 624 61
822 415 61
822 425 61
822 459 61
822 476 61
822 490 61
822 493 61
823 12 61
823 164 61
823 179 61
823 321 61
823 336 61
823 403 61
823 687 61
823 691 61
823 692 61
823 733 61
823 734 61
823 742 61
823 502 61
823 509 61
823 586 61
823 596 61
823 623 61
823 624 61
823 415 61
823 425 61
823 459 61
823 476 61
823 490 61
823 493 61
824 12 61
824 164 61
824 179 61
824 321 61
824 336 61
824 403 61
824 687 61
824 691 61
824 692 61
824 733 61
824 734 61
824 742 61
824 502 61
824 509 61
824 586 61
824 596 61
824 623 61
824 624 61
824 415 61
824 425 61
824 459 61
824 476 61
824 490 61
824 493 61
831 12 61
831 164 61
831 179 61
831 321 61
831 336 61
831 403 61
831 687 61
831 691 61
831 692 61
831 733 61
831 734 61
831 742 61
831 502 61
831 509 61
831 586 61
831 596 61
831 623 61
831 624 61
831 415 61
831 425 61
831 459 61
831 476 61
831 490 61
831 493 61
832 12 61
832 164 61
832 179 61
832 321 61
832 336 61
832 403 61
832 687 61
832 691 61
832 692 61
832 733 61
832 734 61
832 742 61
832 502 61
832 509 61
832 586 61
832 596 61
832 623 61
832 624 61
832 415 61
832 425 61
832 459 61
832 476 61
832 490 61
832 493 61
833 12 61
833 164 61
833 179 61
833 321 61
833 336 61
833 403 61
833 687 61
833 691 61
833 692 61
833 733 61
833 734 61
833 742 61
833 502 61
833 509 61
833 586 61
833 596 61
833 623 61
833 624 61
833 415 61
833 425 61
833 459 61
833 476 61
833 490 61
833 493 61
834 12 61
834 164 61
834 179 61
834 321 61
834 336 61
834 403 61
834 687 61
834 691 61
834 692 61
834 733 61
834 734 61
834 742 61
834 502 61
834 509 61
834 586 61
834 596 61
834 623 61
834 624 61
834 415 61
834 425 61
834 459 61
834 476 61
834 490 61
834 493 61
835 12 61
835 164 61
835 179 61
835 321 61
835 336 61
835 403 61
835 687 61
835 691 61
835 692 61
835 733 61
835 734 61
835 742 61
835 502 61
835 509 61
835 586 61
835 596 61
835 623 61
835 624 61
835 415 61
835 425 61
835 459 61
835 476 61
835 490 61
835 493 61
854 12 61
854 164 61
854 179 61
854 321 61
854 336 61
854 403 61
854 687 61
854 691 61
854 692 61
854 733 61
854 734 61
854 742 61
854 502 61
854 509 61
854 586 61
854 596 61
854 623 61
854 624 61
854 415 61
854 425 61
854 459 61
854 476 61
854 490 61
854 493 61
855 12 61
855 164 61
855 179 61
855 321 61
855 336 61
855 403 61
855 687 61
855 691 61
855 692 61
855 733 61
855 734 61
855 742 61
855 502 61
855 509 61
855 586 61
855 596 61
855 623 61
855 624 61
855 415 61
855 425 61
855 459 61
855 476 61
855 490 61
855 493 61
856 12 61
856 164 61
856 179 61
856 321 61
856 336 61
856 403 61
856 687 61
856 691 61
856 692 61
856 733 61
856 734 61
856 742 61
856 502 61
856 509 61
856 586 61
856 596 61
856 623 61
856 624 61
856 415 61
856 425 61
856 459 61
856 476 61
856 490 61
856 493 61
857 12 61
857 164 61
857 179 61
857 321 61
857 336 61
857 403 61
857 687 61
857 691 61
857 692 61
857 733 61
857 734 61
857 742 61
857 502 61
857 509 61
857 586 61
857 596 61
857 623 61
857 624 61
857 415 61
857 425 61
857 459 61
857 476 61
857 490 61
857 493 61
903 12 61
903 164 61
903 179 61
903 321 61
903 336 61
903 403 61
903 687 61
903 691 61
903 692 61
903 733 61
903 734 61
903 742 61
903 502 61
903 509 61
903 586 61
903 596 61
903 623 61
903 624 61
903 415 61
903 425 61
903 459 61
903 476 61
903 490 61
903 493 61
963 12 61
963 164 61
963 179 61
963 321 61
963 336 61
963 403 61
963 687 61
963 691 61
963 692 61
963 733 61
963 734 61
963 742 61
963 502 61
963 509 61
963 586 61
963 596 61
963 623 61
963 624 61
963 415 61
963 425 61
963 459 61
963 476 61
963 490 61
963 493 61
964 12 61
964 164 61
964 179 61
964 321 61
964 336 61
964 403 61
964 687 61
964 691 61
964 692 61
964 733 61
964 734 61
964 742 61
964 502 61
964 509 61
964 586 61
964 596 61
964 623 61
964 624 61
964 415 61
964 425 61
964 459 61
964 476 61
964 490 61
964 493 61
1045 12 61
1045 164 61
1045 179 61
1045 321 61
1045 336 61
1045 403 61
1045 687 61
1045 691 61
1045 692 61
1045 733 61
1045 734 61
1045 742 61
1045 502 61
1045 509 61
1045 586 61
1045 596 61
1045 623 61
1045 624 61
1045 415 61
1045 425 61
1045 459 61
1045 476 61
1045 490 61
1045 493 61
1200 12 61
1200 164 61
1200 179 61
1200 321 61
1200 336 61
1200 403 61
1200 687 61
1200 691 61
1200 692 61
1200 733 61
1200 734 61
1200 742 61
1200 502 61
1200 509 61
1200 586 61
1200 596 61
1200 623 61
1200 624 61
1200 415 61
1200 425 61
1200 459 61
1200 476 61
1200 490 61
1200 493 61
1201 12 61
1201 164 61
1201 179 61
1201 321 61
1201 336 61
1201 403 61
1201 687 61
1201 691 61
1201 692 61
1201 733 61
1201 734 61
1201 742 61
1201 502 61
1201 509 61
1201 586 61
1201 596 61
1201 623 61
1201 624 61
1201 415 61
1201 425 61
1201 459 61
1201 476 61
1201 490 61
1201 493 61
1202 12 61
1202 164 61
1202 179 61
1202 321 61
1202 336 61
1202 403 61
1202 687 61
1202 691 61
1202 692 61
1202 733 61
1202 734 61
1202 742 61
1202 502 61
1202 509 61
1202 586 61
1202 596 61
1202 623 61
1202 624 61
1202 415 61
1202 425 61
1202 459 61
1202 476 61
1202 490 61
1202 493 61
1757 12 61
1757 164 61
1757 179 61
1757 321 61
1757 336 61
1757 403 61
1757 687 61
1757 691 61
1757 692 61
1757 733 61
1757 734 61
1757 742 61
1757 502 61
1757 509 61
1757 586 61
1757 596 61
1757 623 61
1757 624 61
1757 415 61
1757 425 61
1757 459 61
1757 476 61
1757 490 61
1757 493 61
1895 12 61
1895 164 61
1895 179 61
1895 321 61
1895 336 61
1895 403 61
1895 687 61
1895 691 61
1895 692 61
1895 733 61
1895 734 61
1895 742 61
1895 502 61
1895 509 61
1895 586 61
1895 596 61
1895 623 61
1895 624 61
1895 415 61
1895 425 61
1895 459 61
1895 476 61
1895 490 61
1895 493 61
1896 12 61
1896 164 61
1896 179 61
1896 321 61
1896 336 61
1896 403 61
1896 687 61
1896 691 61
1896 692 61
1896 733 61
1896 734 61
1896 742 61
1896 502 61
1896 509 61
1896 586 61
1896 596 61
1896 623 61
1896 624 61
1896 415 61
1896 425 61
1896 459 61
1896 476 61
1896 490 61
1896 493 61
1897 12 61
1897 164 61
1897 179 61
1897 321 61
1897 336 61
1897 403 61
1897 687 61
1897 691 61
1897 692 61
1897 733 61
1897 734 61
1897 742 61
1897 502 61
1897 509 61
1897 586 61
1897 596 61
1897 623 61
1897 624 61
1897 415 61
1897 425 61
1897 459 61
1897 476 61
1897 490 61
1897 493 61

But my original query correctly gives 47 rows.

DECLARE @SelectedFunds TABLE(pshipid nvarchar(100))
insert into @SelectedFunds

SELECT DISTINCT pshipid FROM GroupPshipAccess GPA
INNER JOIN UserGroup UG ON GPA.UserGroupID = UG.UserGroupID
INNER JOIN UserGroupMember UGM ON GPA.UserGroupID = UGM.UserGroupID
WHERE UserGroupName = 'BLUECREST'
group by pshipid
select * from @SelectedFunds

example

42
660
661
743
744
750
751
752
753
760
775
776
777
781
788
793
794
797
798
799
807
808
813
814
822
823
824
831
832
833
834
835
854
855
856
857
903
963
964
1045
1200
1201
1202
1757
1895
1896
1897

Thanks for your help.

Regards,
sqlnovice123

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-02-14 : 11:41:42

I think this is the way I can set teh parameter passed from to the stored proc and get valid results.

SELECT DISTINCT GPA.pshipid, GPA.UserGroupID, UserID FROM GroupPshipAccess GPA
INNER JOIN UserGroup UG ON GPA.UserGroupID = UG.UserGroupID
INNER JOIN UserGroupMember UGM ON UG.UserGroupID = UGM.UserGroupID
WHERE UserGroupName = @Group
group by GPA.pshipid, GPA.UserGroupID, UGM.UserID
end

SELECT @UserID = UserID from @SelectedFunds

By setting the @UserID, I am ensuring that only users that have access to the ids(etermined by the UserID in the UserMemberGroup table). The table @SelectedFunds has rows inserted from the UserGroupMember table.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-02-15 : 13:39:37

I was able to get this to work. Thanks.

Sqlnovice123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources