How to get a multiple join - 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

How to get a multiple join

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-05-15 : 05:56:33

Hi all,

I got two tables in a one to many relationship

Table1: Course
-------------------
CourseID
CourseName
CourseFees
CourseStartDate
CourseEndDate

Table2: CourseStudymode
---------------------
CourseID
StudyMode

I want to get the results as

CourseName , CourseFEes, CourseStartDate, CourseEndDate, StudyMode (seperated by commas)

For example , given the following data

CourseTable
________________
CourseID = 1
CourseName = 'Basic Accounting'
CourseFee = '$200'
CourseStartDate = '1 Jan 2009'
CourseEndDate = '15 Jan 2009'

CourseID = 2
CourseName = 'Advance Accounting'
CourseFee = '$200'
CourseStartDate = '1 Jan 2009'
CourseEndDate = '15 Jan 2009'

CourseStudyModeTable
----------------------
CourseID: 1
StudyMode: 'Online'

CourseID: 1
StudyMode: 'Instructor Led'

CourseID: 2
StudyMode: 'Distance Learning'

I want to get
CourseID = 1
CourseName = 'Basic Accounting'
CourseFee = '$200'
CourseStartDate = '1 Jan 2009'
CourseEndDate = '15 Jan 2009'
StudyMode = 'Online, Instructor Led'

CourseID = 2
CourseName = 'Advance Accounting'
CourseFee = '$200'
CourseStartDate = '1 Jan 2009'
CourseEndDate = '15 Jan 2009'
StudyMode = 'Distance Learning'

I am having problems trying to join the studymode so that it appears as a column with , for items with multiple entry. Is there any way to get the information to be display as above?

Help is very much appreciated. Thank you very much in advance. Have a wonderful day.

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2009-05-15 : 06:24:00

Try this:
select CourseName+','+ Cast(CourseFee varchar(10)) +','+ CourseStartDate+'', +CourseEndDate +','+StudyMode from Course c inner join CourseStudymode cm on c.CourseID =cm.CourseID

Malay

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-05-15 : 06:29:28

Malay, thank you, but it is not what I want.

I want

Course Id CourseName CourseFee CourseStartDate CourseEndDate StudyMode
1 Basic Accounting 200 1 Jan 2009 15 Jan 2009 Online, Instructor Led
2 Advance Accounting 200 1 Jan 2009 15 Jan 2009 Distance Learning

Note that only the study mode has comma when the it is joined. ;)

Your code will join all as a single string.. :)

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2009-05-15 : 06:41:46

Can you provide the output format with column name. then it will be clear to provide the expected code.

Malay

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-15 : 07:52:05

Can you try this...it has not been tested.

select a.CourseName,
a.CourseFees,
a.CourseStartDate,
a.CourseEndDate,
stuff((select ','+ StudyMode from CourseStudymode where CourseID = b.CourseID for xml path('')),1,1,'')
from Course a inner join
CourseStudymode b on a.CourseID = b.CourseID
group by a.CourseName,a.CourseFees,a.CourseStartDate,a.CourseEndDate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 12:51:48

you can use this.
create a udf like this


CREATE FUNCTION GetStudyModes
(@CourseID int)
RETURNS varchar(8000) 
AS
BEGIN
DECLARE @StudyModeList varchar(8000)

SELECT @StudyModeList=COALESCE(@StudyModeList,'') + StudyMode + ','
FROM Table2
WHERE CourseID=@CourseID

RETURN @StudyModeList
END


then use it like this

SELECT CourseID,CourseName,CourseFees,CourseStartDate,CourseEndDate,
dbo.GetStudyModes(CourseID) AS StudyMode
FROM Table1

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-05-19 : 05:00:54

thank you. :)

check out my blog at http://www.aquariumlore.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 10:09:12

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources