Mending some SQL

[Joe_Fox]

Any one fancy untangling this? It's got lots of errors and I can't figure it out.

The tables used are as follows:

Course
PQE
Location
Responsibility
CourseEvent
Role
DelegateType
CourseType
Group


(I think that's it)



There's a good prize in it for you:

select 
Course."ID",
Course.Title,
Course.Outline,
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"
from
Course, CourseEvent
 
RIGHT OUTER JOIN
CourseEvent ON Course."ID" = CourseEvent.CourseID
 Location, PQE, CourseType, RoleCourse, GroupCourse, 
 DelegateType, Role, [Group], Responsibility
Course 
 
LEFT OUTER JOIN
 CourseEvent ON Course."ID" = CourseEvent.CourseID 
 AND CourseEvent.Deleted = 0 
INNER JOIN
GroupCourse ON GroupCourse.CourseID = Course."ID" AND GroupCourse.CourseID = Course."ID" 
INNER JOIN
[Group] ON GroupCourse.GroupID = [Group]."ID" 
INNER JOIN
PQE ON Course.PQEID = PQE."ID" INNER JOIN 
CourseType ON Course.CourseTypeID = CourseType."ID" 
INNER JOIN 
ResponsibilityCourse ON ResponsibilityCourse.CourseID = Course."ID" 
INNER JOIN
Responsibility ON ResponsibilityCourse.ResponsibilityID = Responsibility."ID" INNER JOIN
Responsibility ON Course.ResponsibilityID = Responsibility."ID" 
 
INNER JOIN
RoleCourse ON Course."ID" = RoleCourse.CourseID 
INNER JOIN
DelegateType ON Course.DelegateTypeID = DelegateType."ID" 
INNER JOIN
Role ON RoleCourse.RoleID = Role."ID" 
LEFT OUTER JOIN
Location ON Location."ID" = CourseEvent.CourseLocation

 

[Darth Vodka]

Any one fancy untangling this? It's got lots of errors and I can't figure it out.

The tables used are as follows:

Course
PQE
Location
Responsibility
CourseEvent
Role
DelegateType
CourseType
Group


(I think that's it)



There's a good prize in it for you:

select 
Course."ID",
Course.Title,
Course.Outline,
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"
from
Course, CourseEvent
 
RIGHT OUTER JOIN
CourseEvent ON Course."ID" = CourseEvent.CourseID
 Location, PQE, CourseType, RoleCourse, GroupCourse, 
 DelegateType, Role, [Group], Responsibility
Course 
 
LEFT OUTER JOIN
 CourseEvent ON Course."ID" = CourseEvent.CourseID 
 AND CourseEvent.Deleted = 0 
INNER JOIN
GroupCourse ON GroupCourse.CourseID = Course."ID" AND GroupCourse.CourseID = Course."ID" 
INNER JOIN
[Group] ON GroupCourse.GroupID = [Group]."ID" 
INNER JOIN
PQE ON Course.PQEID = PQE."ID" INNER JOIN 
CourseType ON Course.CourseTypeID = CourseType."ID" 
INNER JOIN 
ResponsibilityCourse ON ResponsibilityCourse.CourseID = Course."ID" 
INNER JOIN
Responsibility ON ResponsibilityCourse.ResponsibilityID = Responsibility."ID" INNER JOIN
Responsibility ON Course.ResponsibilityID = Responsibility."ID" 
 
INNER JOIN
RoleCourse ON Course."ID" = RoleCourse.CourseID 
INNER JOIN
DelegateType ON Course.DelegateTypeID = DelegateType."ID" 
INNER JOIN
Role ON RoleCourse.RoleID = Role."ID" 
LEFT OUTER JOIN
Location ON Location."ID" = CourseEvent.CourseLocation

this Access & Jet-SQL or Transact-SQL and SQL Server?

 

[Matt_B]

Ugh JOINS, I hate JOINS

 

[Melton Fox]

 

[Darth Vodka]

4 things spring to mind:-

1) i thought double quotes didn't work for fields, i thought it was square brackets
2) you could sub-queries and then link in in stages if that helps
3) melts isn't funny
4) i'm going the pub innabit

 

[Joe_Fox]

use " " or [], doesn't make any difference.
I hate JOINS too!

It's SQL SERVER

Thanks. Joe

 

[Darth Vodka]

ugh tough to debug without query analyser...

i take it you got it in analyser... what line is the error in and what's the message?

 

[Joe_Fox]

Essentially, it is just converting the attached file into SQL and something that will run in query analyzer.

The code beneath the WHERE statement need not be worried about.

View attachment 2181

 

[Darth Vodka]

Essentially, it is just converting the attached file into SQL and something that will run in query analyzer.

The code beneath the WHERE statement need not be worried about.

View attachment 2181

oh a stored procedure to a query, fairy muff

what line is the error on? have you tried building it as smaller queries first?

how's your SQL?

 

[Joe_Fox]

oh a stored procedure to a query, fairy muff

what line is the error on? have you tried building it as smaller queries first?

how's your SQL?

Not at it's peak - I've PMed you.

 

[Macky]

Not at it's peak - I've PMed you.

Why the secrecy? Is it a controversial query?

 

[Matt_B]

Why the secrecy? Is it a controversial query?

Theres certainly an INNER circle feeling surrounding this now

 

[Joe_Fox]

Just didn't want to bore you all with it.
A stored procedure that needs converting to SQL. See attached file on page 1 of this forum.

Any help would be great - it's been doing my head in all day - it's probably easy but I just can't get it working.

 

[Darth Vodka]

OK, very tough without me havnig the tables and query analyzer, otherwise it would be a ten minute job

let's start at the start

does:-

SELECT
Course.[ID],
Course.Title,
Course.Outline,
Course.Presenter,
Course.Timings,
Course.Compulsory,
Course.CPD
FROM Course

work?

 

[Joe_Fox]

yes

 

[Joe_Fox]

OK, very tough without me havnig the tables and query analyzer, otherwise it would be a ten minute job

let's start at the start

does:-

SELECT
Course.[ID],
Course.Title,
Course.Outline,
Course.Presenter,
Course.Timings,
Course.Compulsory,
Course.CPD
FROM Course

work?

Hey Darth,

Yes, this bit works fine. Brings back exactly what you'd expect. What's next?

 

[Durham Fox]

Come on DV, Joe is sat at work twiddling is thumbs until you do his job for him

 

[Joe_Fox]

Lay off Duzza, I'm just after a bit of help. Jeez, some people!

 

[Darth Vodka]

(a quick question: why do you need the PQE table? it appears to be tautological to retrieve the ID field...)

hmm try this instead

and i tell you what, sod naming the fields, use asterisks to pull it all thtough

SELECT Course.*,CourseType.*
FROM Course

JOIN
CourseType ON Course2.[CourseTypeID] = CourseType.[ID]

does that work?

 

[Joe_Fox]

(a quick question: why do you need the PQE table? it appears to be tautological to retrieve the ID field...)

hmm try this instead

and i tell you what, sod naming the fields, use asterisks to pull it all thtough

SELECT Course.*,CourseType.*
FROM Course

JOIN
CourseType ON Course2.[CourseTypeID] = CourseType.[ID]

does that work?

"Course2" threw up an error but removing the 2 meant I got results.