Left Join Not Returning All Rows

[Julian32]

Hello All,

Before this I'd never beefore 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 | File |
| 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 simplicity sake 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.

[Julian32]

For the file_table table the last column should be titled "FileAccess" so that the SQL query makes sense.

Thanks in advance,
Julian

[scripto]

try changing your SQL to read

"WHERE file_table.UserID = 2 or file_table.UserID = null"

[Julian32]

try changing your SQL to read

"WHERE file_table.UserID = 2 or file_table.UserID = null"

Hello scripto,

It still returns:

FileName | FileAccess
file2.txt | file2.txt

For some reason it's not displaying file1.txt from "FileName" with null for the FileAccess row.

Any further help would be appreciated,
Julian

[scripto]

then drop the where clause.

[iam_clint]

this is how it works


when you left join or left outer join

say this is your tables

test1 (table)
id      message
1       hi
2       this
3       is
4       odd
 
test2(table)
id      othermessage
1       crap
2       dude
3       orange
4       cookie
 
SELECT * FROM test1 t1
  LEFT OUTER JOIN test2 t2 ON t1.id = t2.id
  WHERE t1.id = 2
 
result would look something like this
 
id    message   othermessage
2     this      dude
 
if you take the where off you would get something like this
 
id    message    othermessage
1     hi         crap
2     this       dude
3     is         orange
4     odd        cookie
 

[Julian32]

then drop the where clause.

If I drop the Where clause then it would return the FileAccess values of all users, instead of just the user I specify.

[scripto]

Hello All,

Before this I'd never beefore 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 | File |
| 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 simplicity sake 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.

ok one more shot - change the join like this:

LEFT JOIN file_table ON file_list.ListID = file_table.FileID "