Left Join Not Returning All Rows

[Julian32]

Hello All,

Sorry for the double post but I realized this topic would fit better in this forum.

Before this I'd never before needed to use the "LEFT JOIN" command, and unfortunately it's giving me some problems.

I have three tables setup as follows (note they are test tables for a prototype of a larger project):

user_table:
| UserID | Username | Password|
| 1 | julian | abcdef |
| 2 | test | test |


file_table:
| FileID | UserID | FileAccess |
| 1 | 1 | file1.txt|
| 2 | 1 | file2.txt |
| 3 | 2 | file2.txt |

list_table:
| ListID | FileName |
| 1 | file1.txt |
| 2 | file2.txt |
| 3 | file3.txt |

I want to run a query that will display all rows of "FileName" on the second column, and "FileAccess" on the second column IF FileAccess is equal to FileName AND UserID in TableB is equal to a variable which is given a value in a previous part of my program. (For this test it has been given the value two.)

This is my query:

sql = "SELECT file_list.FileName, file_table.FileAccess FROM file_list "
sql = sql & "LEFT JOIN file_table ON file_list.FileName = file_table.FileAccess "
sql = sql & "WHERE file_table.UserID =  2"
 

This is what it returns:

FileName | FileAccess
file2.txt | file2.txt

This is what I WANT it to return:
FileName | FileAccess
file1.txt |
file2.txt | file2.txt

I've tried all types of variations of the query and even tried to make it a right join all without success. Any help with the query will be greatly appreciated.

[MMcCarthy]

Try this:

sql = "SELECT file_List.FileName, IIf(file_table.UserID=2,file_table.FileAccess,"") " & _
"FROM file_List LEFT JOIN file_table " & _
" ON file_List.FileName=file_table.FileName"

I want to run a query that will display all rows of "FileName" on the second column, and "FileAccess" on the second column IF FileAccess is equal to FileName AND UserID in TableB is equal to a variable which is given a value in a previous part of my program. (For this test it has been given the value two.)

if user id is being passed as a variable let me know the variable name and I'll change the above accordingly

[NeoPa]

You seem to have the main table second.
I don't know if this would work with a RIGHT JOIN as I never use them, but this is how I would do it.

BTW I'm assuming file_list maps to list_table in your explanation data.

sql = "SELECT file_list.FileName, file_table.FileAccess " & _
"FROM file_table LEFT JOIN file_list " & _
"ON file_table.FileAccess = file_list.FileName " & _
"WHERE file_table.UserID = 1"

In your code you had the WHERE clause select records where UserID = 2 which would explain your results.

[Julian32]

Hello all,

Thanks to both of you for your help.

I will repost the three tables since as was pointed out by NeoPa I incorrecly named some tables.


user_table:
| UserID | Username | Password|
| 1 | julian | abcdef |
| 2 | test | test |


file_table:
| FileID | UserID | FileAccess |
| 1 | 1 | file1.txt|
| 2 | 1 | file2.txt |
| 3 | 2 | file2.txt |

file_list:
| ListID | FileName |
| 1 | file1.txt |
| 2 | file2.txt |

Basically I want to query these tables to have on the first column a list of the filenames (file_list.FileName), followed by another column with the same value (file_table.FileAccess) if the User has access to the file.

Try this:

sql = "SELECT file_List.FileName, IIf(file_table.UserID=2,file_table.FileAccess,"") " & _
"FROM file_List LEFT JOIN file_table " & _
" ON file_List.FileName=file_table.FileName"



if user id is being passed as a variable let me know the variable name and I'll change the above accordingly

Hello mmcarthy,

I tried this method and it came close to working except that the iif always returns NULL. I end up with this (I changed the my mistaken table and column names so that's not the cause of the error):

FileName | Expr1001
file1.txt |
file2.txt | file2.txt
file2.txt |

I tried SELECT DISTINCT to avoid file2.txt from being queried twice (don't even know why it's doing that since there's only one file2.txt in "FileName") and all it did was move file2.txt on the second column down one row.

You seem to have the main table second.
I don't know if this would work with a RIGHT JOIN as I never use them, but this is how I would do it.

BTW I'm assuming file_list maps to list_table in your explanation data.

sql = "SELECT file_list.FileName, file_table.FileAccess " & _
"FROM file_table LEFT JOIN file_list " & _
"ON file_table.FileAccess = file_list.FileName " & _
"WHERE file_table.UserID = 1"

In your code you had the WHERE clause select records where UserID = 2 which would explain your results.

Hello NeoPa,

The table which I want all rows to be displayed from is file_list since I want to be able to see every file, and a corresponding column with the same file name if the user has access to it. I did try your code and surprisingly it returns the same recordset. BTW the UserId will be compared to another variable which can either have the value of 1 or 2 (to see the File Access Privileges of both user 1 and 2).

Thanks again for your help, if either of you see something I overlooked I'd appreciate any help. In the meanwhile I'll try to do this in two different quieres.

Thanks,
Julian

[MMcCarthy]

try this:

sql = "SELECT file_list.FileName, First(file_table.FileAccess) As FAccess " & _
"FROM file_table RIGHT JOIN file_List " & _
"ON file_table.FileName = file_List.FileName " & _
"WHERE file_table.UserID = 2 " & _
"GROUP BY file_list.FileName;"

BTW I am assuming UserID is a number ...

[Julian32]

try this:

sql = "SELECT file_list.FileName, First(file_table.FileAccess) As FAccess " & _
"FROM file_table RIGHT JOIN file_List " & _
"ON file_table.FileName = file_List.FileName " & _
"WHERE file_table.UserID = 2 " & _
"GROUP BY file_list.FileName;"

BTW I am assuming UserID is a number ...

Hello mmccarthy,

I'm still receiving the same output:

FIleName | FAccess
file2.txt | file2.txt

For some reason it's not displaying file1.txt from FileName and NULL for the corresponding FAccess column. And yes UserID is a number.

Thanks for helping,
Julian

[MMcCarthy]

try this:

 
sql = "SELECT file_list.FileName, " & _
"IIf(IsNull(file_table.FileAccess),"",file_table.FileAccess) As FAccess " & _
"FROM file_table RIGHT JOIN file_List " & _
"ON file_table.FileName = file_List.FileName " & _
"WHERE file_table.UserID = 2 " & _
"GROUP BY file_list.FileName;"
 
 

[MMcCarthy]

Forget the last one, try this instead ...

 
sql = "SELECT file_list.FileName, " & _
"IIf(file_table.UserID=2, file_table.FileAccess, "") As FAccess " & _
"FROM file_table RIGHT JOIN file_List " & _
"ON file_table.FileName = file_List.FileName " & _
"GROUP BY file_list.FileName;"
 
 

[Julian32]

Forget the last one, try this instead ...

 
sql = "SELECT file_list.FileName, " & _
"IIf(file_table.UserID=2, file_table.FileAccess, "") As FAccess " & _
"FROM file_table RIGHT JOIN file_List " & _
"ON file_table.FileName = file_List.FileName " & _
"GROUP BY file_list.FileName;"
 
 

Hello mmccarthy,

I tried that code but I get an error since in the last paremeter of the IIf ("") are not included since they are processed as string start and end quotes. I tried changing "" to '' and get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'IIf(file_table.UserID=2,file_table.FileAccess,'') ' as part of an aggregate function.

Any further help will be greatly appreciated,
Julian

[MMcCarthy]

 
sql = "SELECT file_list.FileName, " & _
"IIf(file_table.UserID=2, file_table.FileAccess,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_List " & _
"ON file_table.FileName = file_List.FileName " & _
"GROUP BY file_list.FileName;"
 
 

Try this ...

[Julian32]

Hello mmcarthy,

This query (I combined it with a previous one you gave me) is very close to working:

sql = "SELECT file_list.FileName, First(IIf(file_table.UserID=1, file_table.FileAccess,Null)) As FAccess " & _
"FROM file_table RIGHT JOIN file_list " & _
"ON file_table.FileAccess = file_list.FileName " & _
"GROUP BY file_list.FileName"

When I tried it with UserID = 2 it worked and produced the desired result. However when I tried it with UserID = 1 it gave me this:

FileName | FAccess
file1.txt | file1.txt
file2.txt |

For some reason it's not displaying the second value in FAccess. Perhaps the FIRST clause is only returning the first row?

Thanks again,
Julian

[MMcCarthy]

sql = "SELECT file_list.FileName, " & _
"IIf(file_table.UserID=2, file_table.FileAccess,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_List " & _
"ON file_table.FileName = file_List.FileName " & _
"GROUP BY file_list.FileName;"

Hello mmcarthy,

This query (I combined it with a previous one you gave me) is very close to working:

sql = "SELECT file_list.FileName, First(IIf(file_table.UserID=1, file_table.FileAccess,Null)) As FAccess " & _
"FROM file_table RIGHT JOIN file_list " & _
"ON file_table.FileAccess = file_list.FileName " & _
"GROUP BY file_list.FileName"

When I tried it with UserID = 2 it worked and produced the desired result. However when I tried it with UserID = 1 it gave me this:

FileName | FAccess
file1.txt | file1.txt
file2.txt |

For some reason it's not displaying the second value in FAccess. Perhaps the FIRST clause is only returning the first row?

Thanks again,
Julian

If you take off the First() statement will it work ..

[Julian32]

sql = "SELECT file_list.FileName, " & _
"IIf(file_table.UserID=2, file_table.FileAccess,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_List " & _
"ON file_table.FileName = file_List.FileName " & _
"GROUP BY file_list.FileName;"




If you take off the First() statement will it work ..

Hello mmccarthy,

When I take out the First () statement it gives me this error:

You tried to execute a query that does not include the specified expression as part of an aggregate function.

I believe it has something to do with the Group by, but if I take it away the first column starts to get duplicated values even with SELECT DISTINCT.

Regards,
Julian

[MMcCarthy]

try this:

sql = "SELECT file_list.FileName, " & _
"IIf(file_table.UserID=2, file_table.FileAccess,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_List " & _
"ON file_table.FileName = file_List.FileName " & _
"GROUP BY file_list.FileName, IIf(file_table.UserID=2, file_table.FileAccess,Null);"

[Julian32]

try this:

sql = "SELECT file_list.FileName, " & _
"IIf(file_table.UserID=2, file_table.FileAccess,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_List " & _
"ON file_table.FileName = file_List.FileName " & _
"GROUP BY file_list.FileName, IIf(file_table.UserID=2, file_table.FileAccess,Null);"

I receive this error:

Syntax error (missing operator) in query expression 'IIf(file_table.UserID=1, file_table.FileAccess,Null) As FAccess'.

Regards,
Julian

[MMcCarthy]

I receive this error:

Syntax error (missing operator) in query expression 'IIf(file_table.UserID=1, file_table.FileAccess,Null) As FAccess'.

Regards,
Julian

did you include the ' As FAccess' in the group by statement?

If you did remove it.

If not post the query as you're using it now.

[Julian32]

did you include the ' As FAccess' in the group by statement?

If you did remove it.

If not post the query as you're using it now.

No I did not remove it, here's the current query:

sql = "SELECT file_list.FileName, IIf(file_table.UserID=1, file_table.FileAccess,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_list " & _
"ON file_table.FileAccess = file_list.FileName " & _
"GROUP BY file_list.FileName, IIf(file_table.UserID=1, file_table.FileAccess,Null) As FAccess "
 

I'm thinking of changing FileAccess to a foreign key, then quering FileAccess based on UserID, saving query to an array, quering FileName, and checking to see if an item in the array matches.

Inneficient I know, but at least I know it will work in the meantime.

Regards,
Julian

[MMcCarthy]

No I did not remove it, here's the current query:

sql = "SELECT file_list.FileName, IIf(file_table.UserID=1, file_table.FileAccess,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_list " & _
"ON file_table.FileAccess = file_list.FileName " & _
"GROUP BY file_list.FileName, IIf(file_table.UserID=1, file_table.FileAccess,Null) As FAccess "
 

REMOVE IT...

Paste in query exactly as below:


sql = "SELECT file_list.FileName, IIf(file_table.UserID=1, file_table.FileAccess,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_list " & _
"ON file_table.FileAccess = file_list.FileName " & _
"GROUP BY file_list.FileName, IIf(file_table.UserID=1, file_table.FileAccess,Null);"

[Julian32]

REMOVE IT...

Paste in query exactly as below:


sql = "SELECT file_list.FileName, IIf(file_table.UserID=1, file_table.FileAccess,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_list " & _
"ON file_table.FileAccess = file_list.FileName " & _
"GROUP BY file_list.FileName, IIf(file_table.UserID=1, file_table.FileAccess,Null);"

Hello mccarthy,

It goes back to what it was doing before the GROUP BY statement, it has repeats in the FileName column. Any way to filter that?

[Julian32]

Thanks to everyone who helped. I fixed the problem by making file_table.FileAccess a foreign key to ListID, and playing around with the ON statement. I thought of swithcing the IIf from the SELECT to the ON and Eureka!!

Here's the final statement:

sql = "SELECT file_list.FileName, file_table.listID FROM file_list LEFT " & _ 
"JOIN file_table ON file_list.ListID = " & _
"IIf(file_table.UserID=1,file_table.ListID,Null)"

I was this close to giving up and choosing to use an inneficient array.

Thanks again,
Julian

[MMcCarthy]

Thanks to everyone who helped. I fixed the problem by making file_table.FileAccess a foreign key to ListID, and playing around with the ON statement. I thought of swithcing the IIf from the SELECT to the ON and Eureka!!

Here's the final statement:

sql = "SELECT file_list.FileName, file_table.listID FROM file_list LEFT " & _ 
"JOIN file_table ON file_list.ListID = " & _
"IIf(file_table.UserID=1,file_table.ListID,Null)"

I was this close to giving up and choosing to use an inneficient array.

Thanks again,
Julian

You're welcom Julian. It's true that most of these problems arise because the table structure is not correct. I should probably have pointed that out.