Another question about joins

[boyratchet via AccessMonster.com]

Students: StudentID, CompanyID
Companies: CompanyID, CompanyName
ClassRegister: ClassRegisterID, StudentID, ClassID
Classes: ClassID, InstructorID, LocationID, StatusID
Instructors: InstructorID, InstructorName
Locations: LocationID, LocationName
Status: StatusID, StatusFlag

Given the tables above (where there is a many to many relationship between
Classes and Students handled by the ClassRegister table), I have been trying
to write a query that will be used in a report with the following structure:

Company
-Location
--Class: Instructor, Status
---Student

I have tried a 100 different queries, and none of them work. I can get part
way there, joining ((Students and Comapnies), ClassRegister), Classes, but
the moment I try to go beyond this, it all goes to pieces.

Any help I could get on this would be appreciated.

BTW Does anybody know of a good book/teaching app for learning SQL?

 

[Allen Browne]

Structure looks good. The only obvious bit that's missing is that one
subject will probably get taught many times over the years, so your Classes
table would probably have a foreign key to a Subjects table, which tells you
what's being taught in a particular class. (Perhaps you have that, but
omitted it here for simplicity.)

So now you created a query using these various tables. Where you have not
set the Required property to Yes for your foreign key fields, you will need
to use outer joins in the query: otherwise you'll lose those records that
are null. Explanation:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

If that's not the issue, can you be a bit more specific about what goes to
pieces? Are you getting wrong records? Missing records? Error messages about
"ambiguous outer joins"? Other?

Here's a starting point on SQL, :
http://allenbrowne.com/bin/Access_Basics_Crystal_080220_Chapter_06.pdf
It's a new 8-page PDF written by Access MVP Crystal

If you want something more, you might try 'SQL Queries for Mere Mortals':
http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&endeca=1&isbn=0321444434&itm=53

 

[boyratchet via AccessMonster.com]

I thought about it a bit more and I came up with something that appears to
work:

SELECT Companies.CompanyName, Students.StudentID, [Students And Classes].
ClassID, Classes.ClassName, Instructors.Instructor, Locations.LocationName,
Status.Status
FROM ((Companies RIGHT JOIN Students ON Companies.CompanyID=Students.
CompanyID)
INNER JOIN [Students And Classes] ON Students.StudentID=[Students And Classes]
.StudentID)
INNER JOIN (((Classes INNER JOIN Instructors ON Classes.
InstructorID=Instructors.InstructorID) INNER JOIN Locations ON Locations.
LocationID=Classes.LocationID) INNER JOIN Status ON Status.StatusID=Classes.
StatusID) ON Classes.ClassID=[Students And Classes].ClassID

This was what I was going for before, but it seems I just grouped the joins
incorrectly.

Since I am pretty new to this, can anybody tell me if this looks..."normal"?
I can't tell if the query is elegantly organized or just a mess that happens
to work. Is there any way of making it more efficient?

Structure looks good. The only obvious bit that's missing is that one
subject will probably get taught many times over the years, so your Classes
table would probably have a foreign key to a Subjects table, which tells you
what's being taught in a particular class. (Perhaps you have that, but
omitted it here for simplicity.)

So now you created a query using these various tables. Where you have not
set the Required property to Yes for your foreign key fields, you will need
to use outer joins in the query: otherwise you'll lose those records that
are null. Explanation:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

If that's not the issue, can you be a bit more specific about what goes to
pieces? Are you getting wrong records? Missing records? Error messages about
"ambiguous outer joins"? Other?

Here's a starting point on SQL, :
http://allenbrowne.com/bin/Access_Basics_Crystal_080220_Chapter_06.pdf
It's a new 8-page PDF written by Access MVP Crystal

If you want something more, you might try 'SQL Queries for Mere Mortals':
http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&endeca=1&isbn=0321444434&itm=53

Students: StudentID, CompanyID
Companies: CompanyID, CompanyName

[quoted text clipped - 23 lines]

BTW Does anybody know of a good book/teaching app for learning SQL?

 

[Allen Browne]

Looks pretty standard.

 

[boyratchet via AccessMonster.com]

Thanks for that. It's nice to know that I'm on the right track.

Looks pretty standard.

I thought about it a bit more and I came up with something that appears to
work:

[quoted text clipped - 13 lines]

Status.StatusID=Classes.
StatusID) ON Classes.ClassID=[Students And Classes].ClassID