Select from 3 tables if result is empty

Question

I'm using 3 tables to collect data from. The proces looks like:

• User write VIN to form
• Script search in table 1 for case_id and country base on that vin number
• After that he use case_id and country for search in table number 2 and get calculation id from there
• Base on that calculation id and case id it search in 3th table

.

My script looks like this:

SELECT 
cases.case_id, 
cases.lastcalc_model_options, 
cases.country, 
calculations.calculation_id, 
calculations.license, 
positions.text 
FROM cases 
INNER JOIN calculations ON(cases.case_id =calculations.case_id 
AND cases.country = calculations.country) 
INNER JOIN positions ON(calculations.case_id = positions.case_id 
AND calculations.calculation_id = positions.calculation_id) 
WHERE vin ='ABCDEFGH'

This select work correctly, problem start when for example there is for example no result in table positions with that case_id and calculation_id. Instead of give back atleast everything it found in other tables it return NOTHING.

Is there a way to change this kind of komplex SELECT to return everything it found not return something only when every condition is TRUE?

Answer 1

Your problem is the INNER JOIN. Using INNER JOIN your result only contains entries present in all tables. Try using LEFT JOIN instead.

SELECT 
cases.case_id, 
cases.lastcalc_model_options, 
cases.country, 
calculations.calculation_id, 
calculations.license, 
positions.text 
FROM cases 
LEFT JOIN calculations ON(cases.case_id =calculations.case_id 
AND cases.country = calculations.country) 
LEFT JOIN positions ON(calculations.case_id = positions.case_id 
AND calculations.calculation_id = positions.calculation_id) 
WHERE vin ='ABCDEFGH'

See this stackoverlow answer for some more indepth information.

Answer 2

INNER JOIN returns rows from both tables only if there is a match between the columns in both tables. You may try LEFT JOIN or FULL OUTER JOIN instead.