Gatimu Gatimu - 3 days ago 6
MySQL Question

Select all values from one table, check another table to see related columns and fetch more values

I really dont know how to phrase my question, probably why google is not giving me results that i need, but am going to try.

I have two tables, required_files table and submitted_files table. I have a page where i want to display to a user all required files for submission and show which files he/she has submitted.

Required files table is as follows:


file_id file_name mandatory
1 Registration Certificate 0
2 KRA Clearance 1
3 3 Months Tax returns 0
4 Business Permit 1
5 Tour Permit 1
6 Country Govt Operating License 0
7 Certificate of good Conduct 0


file_id is unique, mandatory column is binary value to state whether the file is mandatory before registration or not.


submitted files table is a follows

file_id    user_id     file_required_id    original_file_name  file_name_on_server                      submission_date
1 2 2 KRA_Form.docx 0a10f5291e9bcb6a345ac7a8f5705b8a.docx 2016-11-01
2 2 3 Tax_returns.docx 9f04361013df7e25235a03c506f347ed.docx 2016-11-03
3 3 3 Taxes.docx 86aea74cc87fb669510d9d4c488cbcf8.docx 2016-11-04


file_id is unique AI value, user_id col is unique value of the current user logged in, file_required_id column is related to files_required.file_id column


When fetching the values i already have a user_id (in this case, lets use user_id = 2) Now i want to fetch all values of files_required table and check on files submitted table for files that user_id = 2 meaning user has submitted the files.



my sql query is as follows



SELECT files_required.*, submitted_files.* FROM submitted_files
RIGHT JOIN files_required ON files_required.id = submitted_files.file_required_id
WHERE submitted_files.user_id = 2




This gives me two rows only where the user_ids matched but i want the entire files_required table values and show which files the user has submitted. Someone Kindly assist.




In the meantime, i am fetching files_requied table first then looping through the other table using a php script to look for submitted files for the given user. it works but its not what i wanted and is cumbersome and a rookie move.

Answer

Try having user_id condition in RIGHT JOIN itself like below query

SELECT files_required.*, submitted_files.* 
FROM submitted_files
RIGHT JOIN files_required ON  files_required.id = submitted_files.file_required_id
 AND submitted_files.user_id = 2
Comments