Different types of joins

[beckyr51]

Ive a question relating to joins, in the code below im trying to find a tutor, who has free space, is in a certain faculty, doesnt have TU_ONLY and who doesnt have a preference for a certain course.
Basically it works for the first three criteria but it only returns a tutor who has an entry in tblPreference. A tutor does not need to have an entry in this table so i just want to find a tutor as long as they dont have a preference for this course.
Is it to do with my join-ive tried both inner and left joins


strsql2 = "SELECT TOP 1 tblTutor.TU_CODE " _
& "FROM (tblTutor LEFT JOIN [tblStudents] ON [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE) " _
& "inner join tblPreference on (tblTutor.TU_CODE = tblPreference.TU_CODE) " _
& "GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_DP_NO, tblTutor.TU_FAC_NO, tblTutor.TU_COURSE, tblTutor.TU_ONLY, tblStudents.STU_TU_CODE, tblPreference.TU_CODE, tblPreference.TU_COURSE " _
& "HAVING ([TU_CHAMBER_SIZE] > (Count([tblStudents].STU_TU_CODE)) AND tblTutor.[TU_FAC_NO] = " & fac & " AND tblTutor.[TU_ONLY] = false AND tblPreference.[TU_COURSE] <> '" & course & "')"

 

[ck1999]

Do not use an inner join with tblpreference if they do not have to have an entry in this table. Use a left join instead.

ck1999

 

[beckyr51]

Some tutors do have an entry in this table and some dont, is there a single piece of code i can use or do i have to do some kind of search and then a certain type of join depending on the result of the search?

 

[JoeAtWork]

Have you tried ck1999's suggestion?

 

[ck1999]

I think you should do inner join and then the HAVING statement where tblpreference.FIELDID <> "Course"

ck1999

 

[beckyr51]

Sorry im not following ck1999, you say i should always use an inner join? course is a recordset item (course = rs("STU_COURSE_CODE")) so having <> "course" doesnt work

 

[ck1999]

1st I did not say ALWAYS.

Second, I was not being literal but telling you the a thought process of your statement.


try this

strsql2 = "SELECT TOP 1 tblTutor.TU_CODE " _
                & "FROM (tblTutor LEFT JOIN [tblStudents] ON [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE) " _
                & "left join tblPreference on (tblTutor.TU_CODE = tblPreference.TU_CODE) " _
                & "GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_DP_NO, tblTutor.TU_FAC_NO, tblTutor.TU_COURSE, tblTutor.TU_ONLY, tblStudents.STU_TU_CODE, tblPreference.TU_CODE, tblPreference.TU_COURSE " _
                & "HAVING ([TU_CHAMBER_SIZE] > (Count([tblStudents].STU_TU_CODE)) AND tblTutor.[TU_FAC_NO] = " & fac & " AND tblTutor.[TU_ONLY] = false AND tblPreference.[TU_COURSE] <> '" & course & "')"

ck1999

 

[beckyr51]

Sorry i hope you didnt think i was being rude, i'd already tried using a left join and got the same results. Maybe i didnt explain properly what im trying to do.
The tutor im trying to select can either have no entries in tblPreference or some entries in tblPreference (as long as their TU_COURSE in this tblPreference does not match 'course')
So im trying to write code that finds the first tutor whether or not they have entries in tblPreference

 

[ck1999]

And what do you think a left join will give you!!!


What results are you getting of the query I just posted?


ck1999

 

[beckyr51]

Im getting the same results as when i use an inner join, when i know i should be getting a different tutor that does not have an entry in tblPreferences

 

[ck1999]

try

strsql2 = "SELECT tblTutor.TU_CODE " _
& "FROM (tblTutor LEFT JOIN [tblStudents] ON [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE) " _
& "left join tblPreference on (tblTutor.TU_CODE = tblPreference.TU_CODE) " _
& "GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_DP_NO, tblTutor.TU_FAC_NO, tblTutor.TU_COURSE, tblTutor.TU_ONLY, tblStudents.STU_TU_CODE, tblPreference.TU_CODE, tblPreference.TU_COURSE;

 

[beckyr51]

Yes that shows the right tutors for most apart from a tutor who has one of the courses in tblPreference

 

[ck1999]

I do not know why it is only showing tutors who has a course in tblpreference since it is a left join it should be irrelevant of the tblpreference

ck1999

 

[beckyr51]

Yes it is odd, as soon as i add in the tblPreference.[TU_COURSE] <> '" & course & "' criteria it starts to only take tutors who have entries in that table

 

[ck1999]

Am I understand correctly and that you tried

strsql2 = "SELECT tblTutor.TU_CODE " _
                & "FROM (tblTutor LEFT JOIN [tblStudents] ON [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE) " _
                & "left join tblPreference on (tblTutor.TU_CODE = tblPreference.TU_CODE) " _
                & "GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_DP_NO, tblTutor.TU_FAC_NO, tblTutor.TU_COURSE, tblTutor.TU_ONLY, tblStudents.STU_TU_CODE, tblPreference.TU_CODE, tblPreference.TU_COURSE;"

exactly and it still

shows the right tutors for most apart from a tutor who has one of the courses in tblPreference

Please elaborate on this result or if possible post results of this code and also what you would of expected

ck1999

 

[beckyr51]

Sorry; when i tried the above it doesnt give the right tutor codes because i need the constraints in (being in the same faculty, having space etc)

When i tried the above with HAVING ([TU_CHAMBER_SIZE] > (Count([tblStudents].STU_TU_CODE)) AND tblTutor.[TU_FAC_NO] = " & fac & " AND tblTutor.[TU_ONLY] = false
It worked (as in found tutors some of which where in tblPreference and some which were not) but i do need to have this constraint in


For example:
Student No. 2531 goes to TU_CODE 005 (this is right)
Student No. 2533 goes to TU_CODE 69 (this is also right)
Student No. 2534 goes to TU_CODE 77 (but this cant be because this tutor has this students course in tblPreference)

But when i add in tblPreference.[TU_COURSE] <> '" & course & "' it returns tutors all of which have entries in tblPreference.

For example:
Student No. 2531 goes to TU_CODE 31 (this is the first tutor in tblPrefernce with free space in the students faculty but as above they should have been assigned to 005)
Student No. 2533 goes to TU_CODE 69 (this is right but i think its only because its the first tutor in tblPrefernce with free space in the students faculty)
Student No. 2534 goes to TU_CODE 83 (this is technically right as TU_CODE 77 contains that students course in tblPreference but 83 is also the second tutor in tblPreferene)
So as you can see they all come from table preference

 

[ck1999]

What I am trying to determine is if the sql is not querying correctly as far as the left join or are you not getting the correct record as your top1.


This is why I am curious of the dataset the query gives you for

strsql2 = "SELECT tblTutor.TU_CODE " _
                & "FROM (tblTutor LEFT JOIN [tblStudents] ON [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE) " _
                & "left join tblPreference on (tblTutor.TU_CODE = tblPreference.TU_CODE) " _
                & "GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_DP_NO, tblTutor.TU_FAC_NO, tblTutor.TU_COURSE, tblTutor.TU_ONLY, tblStudents.STU_TU_CODE, tblPreference.TU_CODE, tblPreference.TU_COURSE;"

That is why I changed the select to "select tbltutor.TU_Code and not SELECT TOP 1 tblTutor.TU_CODE.

Could you run the following in a new query and not as part of your code

1 query

SELECT tblTutor.TU_CODE 
FROM (tblTutor LEFT JOIN [tblStudents] ON [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE) left join tblPreference on (tblTutor.TU_CODE = tblPreference.TU_CODE) 
GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_DP_NO, tblTutor.TU_FAC_NO, tblTutor.TU_COURSE, tblTutor.TU_ONLY, tblStudents.STU_TU_CODE, tblPreference.TU_CODE, tblPreference.TU_COURSE HAVING ([TU_CHAMBER_SIZE] > (Count([tblStudents].STU_TU_CODE)) AND tblTutor.[TU_FAC_NO] = " & fac & " AND tblTutor.[TU_ONLY] = false;

replace & fac & with an actual string you know is in your table

and then query2

SELECT tblTutor.TU_CODE 
FROM (tblTutor LEFT JOIN [tblStudents] ON [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE) left join tblPreference on (tblTutor.TU_CODE = tblPreference.TU_CODE) 
GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_DP_NO, tblTutor.TU_FAC_NO, tblTutor.TU_COURSE, tblTutor.TU_ONLY, tblStudents.STU_TU_CODE, tblPreference.TU_CODE, tblPreference.TU_COURSE HAVING ([TU_CHAMBER_SIZE] > (Count([tblStudents].STU_TU_CODE)) AND tblTutor.[TU_FAC_NO] = " & fac & " AND tblTutor.[TU_ONLY] = false AND tblPreference.[TU_COURSE] <> '" & course & "')

replace & fac & with an actual string you know is in your table
and replace '" & course & "' with an actual string you know is in your table

let see how many records you get for each query and if they are correct.

ck1999