Can I include a where clause before Inner join clause

Question

I have This code

$q = $conn->query("SELECT facultydetails.F_NAME,Paper_title from faculty inner join facultydetails on faculty.Fac_ID = facultydetails.Fac_ID where (paper_path = 'NULL' OR paper_path = '') and  (certificate_path = 'NULL' OR certificate_path = '') and (report_path = 'NULL' OR report_path = '') " );

Now I need to add a condition where a user will give the id and only That id related stuff should be picked can I use this code

$q = $conn->query("SELECT facultydetails.F_NAME,Paper_title from faculty where facultydetails.Fac_ID='$FacID' inner join facultydetails on faculty.Fac_ID = facultydetails.Fac_ID where (paper_path = 'NULL' OR paper_path = '') and  (certificate_path = 'NULL' OR certificate_path = '') and (report_path = 'NULL' OR report_path = '') " );

Working in php

I hope this will work for you. https://stackoverflow.com/questions/10133356/where-clause-before-inner-join — Bhavesh Tailor, Jun 26 '19 at 11:19

score 2 · Answer 1 · answered Jun 26 '19 at 11:35

2

No you can not add the where clause before the inner join.

Syntax for the inner join is as below:

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;

answered Jun 26 '19 at 11:35

Shivani Sonagara

1,299
9
21

score 0 · Answer 2 · edited Jun 26 '19 at 11:17

0

SELECT facultydetails.F_NAME,Paper_title 
from faculty 
inner join facultydetails on faculty.Fac_ID = facultydetails.Fac_ID 
where facultydetails.Fac_ID='$FacID' 
AND (paper_path = 'NULL' OR paper_path = '') and  (certificate_path = 'NULL' OR certificate_path = '') and (report_path = 'NULL' OR report_path = '')

edited Jun 26 '19 at 11:17

Claudio

5,078
1
22
33

answered Jun 26 '19 at 11:16

Avinash Rathod

86
8

1

One SQL can only have one where clause you can use it as above – Avinash Rathod Jun 26 '19 at 11:17

score 0 · Accepted Answer · answered Jun 26 '19 at 11:49

You cannot add a where before the inner join, but you can add your condition into the on clause, like:

"SELECT facultydetails.F_NAME,Paper_title ".
"from faculty ".
"inner join facultydetails ".
"on (facultydetails.Fac_ID='$FacID') and (faculty.Fac_ID = facultydetails.Fac_ID) ".
"where (paper_path = 'NULL' OR paper_path = '') and  (certificate_path = 'NULL' OR certificate_path = '') and (report_path = 'NULL' OR report_path = '') "

or you can prefilter your faculty table, like:

"SELECT facultydetails.F_NAME,Paper_title ".
"from (select * from faculty where (facultydetails.Fac_ID='$FacID')) f ".
"inner join facultydetails ".
"on f.Fac_ID = facultydetails.Fac_ID ".
"where (paper_path = 'NULL' OR paper_path = '') and  (certificate_path = 'NULL' OR certificate_path = '') and (report_path = 'NULL' OR report_path = '') "

EDIT:

In SQL having a code "above" or "below" some other part inside a single query will not guarantee that it is executed before it.

Can I include a where clause before Inner join clause

3 Answers3