How can I list other matching values even if there is an unmatched value in the query?-CodePudding

In my query there is a value that will not match in the demand category table. Therefore, since one value does not match in the output of my query, other matching values do not appear.

I want to do; How can I list other matching values even if there is an unmatched value in the query?

process Table

fk_unit_id  fk_unit_position fk_demand_category
     1             2                  1

unit table

unit_id 
    1

unit_position table

unit_position
    2

demand_category table

demand_category
     1

Query:

SELECT unit_name,unit_position_name,demand_category_name From process 
INNER JOIN unit ON process.fk_unit_id = unit_id and unit_id =1
INNER JOIN unit_position ON process.fk_unit_position_id = unit_position_id and unit_position_id = 2
INNER JOIN demand_category ON process.fk_demand_category_id = demand_category_id  and  demand_category_id =0 ;

CodePudding user response：

Switch INNER JOIN on demand_category with LEFT JOIN

LEFT JOIN gets all records from the LEFT linked and the related record from the right table ,but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL.

SELECT unit_name,unit_position_name,demand_category_name From process 
INNER JOIN unit ON process.fk_unit_id = unit_id and unit_id =1
INNER JOIN unit_position ON process.fk_unit_position_id = unit_position_id and unit_position_id = 2
LEFT JOIN demand_category ON process.fk_demand_category_id = demand_category_id  and  demand_category_id =0 ;

CodePudding user response：

You can use outer join to have the columns that don't match, just the corresponding values in other table will be padded with null. Other way is to use IN operator, but slower query performance.