CODEWITHSUNDEEP
mysql
Gatimu
Gatimu

Reputation: 21

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.

Upvotes: 0

Views: 1568

Answers (2)

Viki888
Viki888

Reputation: 2774

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

Upvotes: 1

FallAndLearn
FallAndLearn

Reputation: 4135

You want this. 

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

Don't put the where condition on userid as it will filter out the data just for that user. You want all the records and user should also be seen. Just put the user_id in the select statement.

Upvotes: 0

Related Questions