469,282 Members | 1,708 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Left Join Not Returning All Rows

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:

Expand|Select|Wrap|Line Numbers
  1. sql = "SELECT file_list.FileName, file_table.FileAccess FROM file_list "
  2. sql = sql & "LEFT JOIN file_table ON file_list.FileName = file_table.FileAccess "
  3. sql = sql & "WHERE file_table.UserID =  2"
  4.  
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.
Oct 29 '06 #1
7 2001
For the file_table table the last column should be titled "FileAccess" so that the SQL query makes sense.

Thanks in advance,
Julian
Oct 29 '06 #2
scripto
143 100+
try changing your SQL to read

"WHERE file_table.UserID = 2 or file_table.UserID = null"
Oct 30 '06 #3
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
Oct 30 '06 #4
scripto
143 100+
then drop the where clause.
Oct 30 '06 #5
iam_clint
1,208 Expert 1GB
this is how it works


when you left join or left outer join

say this is your tables
Expand|Select|Wrap|Line Numbers
  1. test1 (table)
  2. id      message
  3. 1       hi
  4. 2       this
  5. 3       is
  6. 4       odd
  7.  
  8. test2(table)
  9. id      othermessage
  10. 1       crap
  11. 2       dude
  12. 3       orange
  13. 4       cookie
  14.  
  15. SELECT * FROM test1 t1
  16.   LEFT OUTER JOIN test2 t2 ON t1.id = t2.id
  17.   WHERE t1.id = 2
  18.  
  19. result would look something like this
  20.  
  21. id    message   othermessage
  22. 2     this      dude
  23.  
  24. if you take the where off you would get something like this
  25.  
  26. id    message    othermessage
  27. 1     hi         crap
  28. 2     this       dude
  29. 3     is         orange
  30. 4     odd        cookie
  31.  
Oct 30 '06 #6
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.
Oct 30 '06 #7
scripto
143 100+
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:

Expand|Select|Wrap|Line Numbers
  1. sql = "SELECT file_list.FileName, file_table.FileAccess FROM file_list "
  2. sql = sql & "LEFT JOIN file_table ON file_list.FileName = file_table.FileAccess "
  3. sql = sql & "WHERE file_table.UserID =  2"
  4.  
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 "
Oct 30 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by StealthBananaT | last post: by
1 post views Thread by Jamie Burns | last post: by
1 post views Thread by Quarco | last post: by
4 posts views Thread by polycom | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.