How to filter records using JOIN and WHERE statement?

My query

SELECT table1.name, table2.wage, table2.bonus table3.shift, table4.vacation
   FROM table1
INNER JOIN table2 ON 开发者_C百科table1.userid = table2.userid
INNER JOIN table3 ON table2.userid = table3.userid
INNER JOIN table4 ON table4.userid = table4.userid 
LEFT JOIN table5 ON table1.name = table5.position
**LEFT JOIN vacation on vacation.userid = table1.userid
WHERE vacation.userid IS NULL**;

This selects all where userid is NOT inside vacation table.

Now i need to check if userid IS inside vacation table and if vacation.status = 1 for this userid should still show in query.

If this is a separate query/requirement, then the following query will work:

SELECT table1.name, table2.wage, table2.bonus table3.shift, table4.vacation
   FROM table1
INNER JOIN table2 ON table1.userid = table2.userid
INNER JOIN table3 ON table2.userid = table3.userid
INNER JOIN table4 ON table4.userid = table4.userid 
INNER JOIN vacation on vacation.userid = table1.userid
LEFT JOIN table5 ON table1.name = table5.position
WHERE vacation.status = 1;

If you need both requirements (userid is NULL or status = 1), try this:

SELECT table1.name, table2.wage, table2.bonus table3.shift, table4.vacation
   FROM table1
INNER JOIN table2 ON table1.userid = table2.userid
INNER JOIN table3 ON table2.userid = table3.userid
INNER JOIN table4 ON table4.userid = table4.userid 
LEFT JOIN table5 ON table1.name = table5.position
LEFT JOIN vacation on vacation.userid = table1.userid
WHERE vacation.user_id IS NULL or vacation.status = 1;