Mending some SQL

[Joe_Fox]

and yeah, sod the PQE table.

 

[Darth Vodka]

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

oh yeah my bad...

i'd just try and use asteriskes to keep it simple...try

SELECT Course.*,CourseType.*,CourseEvent.*,Location.*
FROM ((( Course
JOIN
CourseType ON Course.[CourseTypeID] = CourseType.[ID] )
JOIN
CourseEvent ON Course.[ID] = CourseEvent.CourseID )
JOIN
Location ON Location.[ID] = CourseEvent.CourseLocation )

try to join in one table at a time... can't remeber the exact syntax w/o analyser

 

[Joe_Fox]

oh yeah my bad...

i'd just try and use asteriskes to keep it simple...try

SELECT Course.*,CourseType.*,CourseEvent.*,Location.*
FROM ((( Course
JOIN
CourseType ON Course.[CourseTypeID] = CourseType.[ID] )
JOIN
CourseEvent ON Course.[ID] = CourseEvent.CourseID )
JOIN
Location ON Location.[ID] = CourseEvent.CourseLocation )

try to join in one table at a time... can't remeber the exact syntax w/o analyser

That executes fine. Any more to throw at it?

 

[Darth Vodka]

That executes fine. Any more to throw at it?

you see the gist of what i'm doing, linking in the tables?

just link them all in

then after that works, worry about your joins.. you may have it easy and only need inner joins

you could always link all the tables into access and use query builder...then read off the SQL (it will be JET SQL but it's fairly similar)

oh and with the asteriskes, you'll be selecting loads of fields, you may want to select less

 

[Joe_Fox]

This is the bit that needs unravelling:

FROM'
Course, CourseEvent, '
Course 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 '

It's a case of understanding what each join does. What does a Right Outer Join do, what does an Inner join do and what does a Left outer join do? And how do they tie into this code?

Also, I believe the above could be easier to understand if organised a bit better.

Darth, for reference, I've identified the tables in question and summarised what is contained in them below:

Course - contains required data
CourseEvent - contains required data
Location - contains required data
PQE - contains required data
CourseType - contains required data
RoleCourse - seems to be a lookup table
GroupCourse - seems to be a lookup table
DelegateType - contains required data
Role - contains required data
Responsibility - contains required data
ResponsibilityCourse - seems to be a lookup table

Hope that helps a bit.
Joe_Fox

 

[Joe_Fox]

you see the gist of what i'm doing, linking in the tables?

just link them all in

then after that works, worry about your joins.. you may have it easy and only need inner joins

you could always link all the tables into access and use query builder...then read off the SQL (it will be JET SQL but it's fairly similar)

oh and with the asteriskes, you'll be selecting loads of fields, you may want to select less

I do see the gist, just that eventually the results produced get minimal - I guess this is where the left, right joins come in?

 

[Darth Vodka]

ah, what are joins?

ok

http://en.wikipedia.org/wiki/Join_(SQL)

an INNER join produces the set of records only when the matching fields are present in BOTH tables

an outer join produces all of one table and produces nulls for the corresponding other side

and left and right just refer to which table you are taking all of the results from

read wiki and inwardly digest

how is your Access?

 

[Darth Vodka]

I do see the gist, just that eventually the results produced get minimal - I guess this is where the left, right joins come in?

oh if the results are getting minimal then i need to worry about the joins...urk

tell you what...

link all the tables into access, copy them from linked tables into normal tables then email the .mdb ?

aren't i nice!

 

[Joe_Fox]

oh if the results are getting minimal then i need to worry about the joins...urk

tell you what...

link all the tables into access, copy them from linked tables into normal tables then email the .mdb ?

aren't i nice!

If only I had access on my machine!

 

[Darth Vodka]

If only I had access on my machine!

they haven't given you Access, but you're expected to sort SQL server out?

download the free version of Access 2007 then do it

or...how big are these tables?

 

[Joe_Fox]

They aren't that big.

I have SQL Server Management Studio and SQL Server Business Intelligence Development Studio. Any good or do I really need access?

 

[Darth Vodka]

They aren't that big.

I have SQL Server Management Studio and SQL Server Business Intelligence Development Studio. Any good or do I really need access?

email me the tables, either as text files or in a spready

i'll build it in access you and email you the sql (it'll be Jet SQL though)

oh, i'll PM my email

 

[Steven]

email me the tables, either as text files or in a spready

i'll build it in access you and email you the sql (it'll be Jet SQL though)

oh, i'll PM my email

As you agent your fee is £100 per hour or part thereof and my fee is 20%

 

[Hazzman]

I had to go into Waterstone at lunch to get a uni book.

I noticed SQL For Dummies and this thread came immediately to mind

 

[Darth Vodka]

As you agent your fee is £100 per hour or part thereof and my fee is 20%

you setting me up as a limited company then? and doing my admin?

I had to go into Waterstone at lunch to get a uni book.
I noticed SQL For Dummies and this thread came immediately to mind

i have "SQL in 5 minutes" (N.B. small print says 5 minutes a chapter)

it's probably about a 30 minute job...if i had sql server and the tables in front of me...i quite enjoy transact-sql

 

[Steven]

you setting me up as a limited company then? and doing my admin?



i have "SQL in 5 minutes" (N.B. small print says 5 minutes a chapter)

it's probably about a 30 minute job...if i had sql server and the tables in front of me...i quite enjoy transact-sql

As your agent I won't insist that I deal with a UK registered Company.

 

[Darth Vodka]

As your agent I won't insist that I deal with a UK registered Company.

your 20% will be greater if i don't pay income tax...



i was contracting in the last job... licence to print money

 

[Joe_Fox]

have PMed you Darth

 

[Darth Vodka]

a few questions:-

1) does the stored procedure work?
2) do you need to pull the exact same fields as the SP?

i don't really see why this is so tricky?

i get

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;

to work in Access

just build it up table by table and use outer joins from Course and from CourseEvents as the lookup tables may not have the things you are looking up in them?



try it yourself in Access

 

[Joe_Fox]

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'.