Distinct records from multiple tables

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

SQL Server Development (2000)

Distinct records from multiple tables

Author

Topic

php95saj
Starting Member

43 Posts

Posted - 2002-05-07 : 12:09:57

I am trying to pull records from three tables, but don;t want to have repetition in the recordset returned.
I have a profile table with
tblProfile
profileID (PIK),
profileName

tblProfileUsers
profileID,
userID

tblProfileLanguages
profileID,
languageID

A rpofile can have more than one authorized users and simmilarly a profile can have more than one languages.

I was wondering if there was a way to return all information in one stored procedure.

TIA

Sharjeel

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-07 : 12:59:30

provide sample data and the desired resultset(s) . . . your requirements . . .

quote:
I was wondering if there was a way to return all information in one stored procedure.

. . . are not clear enuff.

<O>

php95saj
Starting Member

43 Posts

Posted - 2002-05-07 : 13:09:06

OK
tblProfile

profileID -- profileName
----------------------------

1029 Test Profile1

tblProfileUsers

profileID --- userID
--------------------------
1029 384
1029 451
1029 298
1035 120

tblProfileLanguages

profileID --- languageID
----------------------------
1029 34
1035 10
1029 59
1029 1

etc...

Now I want to pull entries frotblProfile for profileID=1029, at the same time I want to pull results from tblProfileUsres and tblProfileLangauges as well for this profile to show which users are in the profiles and what languages are in the profile.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-08 : 06:08:04

Please post the recordset you expect as the result.
Without the desired output, I can only guess that you want either one recordset or 3 separate recordsets.

1. One Recordset
create procedure up_GetProfileDetails (@profileID int)
AS
select 'P', profileName from #tblProfile where profileID = @profileID
union
select 'U', UserName from #tblProfileUsers INNER JOIN #Users ON #Users.ID = #tblProfileUsers.userID where profileID = @profileID
union
select 'L', LanguageName from #tblProfileLanguages INNER JOIN #Languages ON #Languages.ID = #tblProfileLanguages.LanguageID where profileID = @profileID
GO

2. Three recordsets.
create procedure up_GetProfileDetails (@profileID int)
AS
select * from #tblProfile where profileID = @profileID

select * from #tblProfileUsers where profileID = @profileID

select * from #tblProfileLanguages where profileID = @profileID
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources