Mending some SQL

[Steven]

I haven't got access mate. That code produced an error (see below):

SELECT 
Course.[ID], 
Course.Title, 
Course.Outline, 
Course.CPD, 
Course.Presenter, 
Course.Timings, 
Course.Compulsory, 
CourseEvent.CourseLocation, 
CourseEvent.CourseDate, 
CourseEvent.CourseTime
FROM 
(((((((Course RIGHT JOIN CourseEvent ON Course.[ID] = CourseEvent.CourseID) 
LEFT JOIN GroupCourse ON Course.[ID] = GroupCourse.CourseID) 
LEFT JOIN [Group] ON GroupCourse.GroupID = Group.[ID]) 
LEFT JOIN PQE ON Course.PQEID = PQE.[ID]) 
LEFT JOIN ResponsibilityCourse ON Course.[ID] = ResponsibilityCourse.CourseID) 
LEFT JOIN Responsibility ON ResponsibilityCourse.ResponsibilityID = Responsibility.ID) 
LEFT JOIN Location ON CourseEvent.CourseLocation = Location.[ID]) 
LEFT JOIN RoleCourse ON Course.ID = RoleCourse.CourseID)

Server: Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'Group'.

[Group] - Remove those brackets.

 

[Darth Vodka]

[Group] - Remove those brackets.

group is a key word and it'll need the brackets to know it's a table and not you trying to group thing

 

[Darth Vodka]

LEFT JOIN [Group] ON GroupCourse.GroupID = [Group].[ID])

try square brackets around the second group

 

[Joe_Fox]

That sorted it, thanks.

Now the problem is getting all the data I require back:

SELECT
Course.ID 			
Course.Title 			
Course.Outline 			
PQE.Description 	
PQE.ID 			
Course.Presenter		
Course.Timings			
Course.Compulsory 	
Course.CPD 		
CourseType.Description 	
CourseType.ID 		
CourseEvent.ID 			
CourseEvent.CourseLocation 
Location.Description 		
Responsibility.Description 	
Responsibility.ID 					
CourseEvent.CourseGroup 	
CourseEvent.CourseDate 		
CourseEvent.CourseTime 		
Role.Description 	
Role.ID 		
DelegateType.[Delegate Type] 	
DelegateType.ID 		
[Group].ID 		
[Group].Description

There seems to be a problem with the Role and the DelegateType tables.

I get the following error:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'Role' does not match with a table name or alias name used in the query.

 

[Darth Vodka]

That sorted it, thanks.
I get the following error:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'Role' does not match with a table name or alias name used in the query.

hold on, my bad, i think i forgot to include it

 

[Darth Vodka]

SELECT CourseEvent.*
FROM (((((((((Course RIGHT JOIN CourseEvent ON Course.ID = CourseEvent.CourseID) LEFT JOIN GroupCourse ON Course.ID = GroupCourse.CourseID) LEFT JOIN [Group] ON GroupCourse.GroupID = Group.ID) LEFT JOIN PQE ON Course.PQEID = PQE.ID) LEFT JOIN ResponsibilityCourse ON Course.ID = ResponsibilityCourse.CourseID) LEFT JOIN Responsibility ON ResponsibilityCourse.ResponsibilityID = Responsibility.ID) LEFT JOIN Location ON CourseEvent.CourseLocation = Location.ID) LEFT JOIN RoleCourse ON Course.ID = RoleCourse.CourseID) LEFT JOIN Role ON RoleCourse.RoleID = Role.ID) LEFT JOIN DelegateType ON Course.DelegateTypeID = DelegateType.ID;

use that bit for your joins and select whatever you like

you'll need to correct the [group] thing again (a translation from jet sql to t-sql anomaly i think)

 

[Steven]

group is a key word and it'll need the brackets to know it's a table and not you trying to group thing

Group is a keyword. :icon_redf :icon_redf :icon_redf Who would name a table the same as a keyword?

 

[Macky]

Group is a keyword. :icon_redf :icon_redf :icon_redf Who would name a table the same as a keyword?

I think we'll be able to work out who the culprit is

 

[Darth Vodka]

Group is a keyword. :icon_redf :icon_redf :icon_redf Who would name a table the same as a keyword?

yes that is a bit daft

at least it isn't called "table1"

 

[Joe_Fox]

It wasn't me - it's an old application that I am in the process of replacing but I need all the raw data that this stored procedure extracts.

 

[Joe_Fox]

Ok, this is doing my head in now.

Who can advise on this:

If a field in a table called location has multiple values, say the location field has "leicester, derby" and that table is related to another table called course, when trying to get the course back, how do I stop it from producing the following result for each occurence where there is multiple values:

course      location
football    leicester  
football    derby
tennis      leicester
tennis      derby

I just want:

course      location
football    leicester, derby  
tennis      leicester, derby

 

[Darth Vodka]

Ok, this is doing my head in now.

Who can advise on this:

If a field in a table called location has multiple values, say the location field has "leicester, derby" and that table is related to another table called course, when trying to get the course back, how do I stop it from producing the following result for each occurence where there is multiple values:

course      location
football    leicester  
football    derby
tennis      leicester
tennis      derby

I just want:

course      location
football    leicester, derby  
tennis      leicester, derby

get to grips with cross tab queries...then i'll tell you how to turn that into what you want...

 

[Joe_Fox]

I have got to grips with it but just need to get multiple values in some of the fields.

 

[Darth Vodka]

I have got to grips with it but just need to get multiple values in some of the fields.

after the cross tab, write a new field, something like:-

LocationMultiple:iif([Leicester]>0,"Leicester ","") & iif([Derby]>0,"Derby ","") & iif([Coventry]>0,"Coventry ","")