MySql query Get all rows from left table and common from right table-CodePudding

Table 1 - customer_kitchen_service_plans and data https://prnt.sc/00_ip7uWiQuq

Table 2 - kitchen_service_plans_linking and data https://prnt.sc/e_GW64THTCFK

Above two are the tables and i want to join in such a way that it should return All the rows from Table 1 and Common rows from Table 2

Join using column kitchen_service_plan_id from Table 1 and kitchen_service_plan_parent_id from Table 2

Current query is as below

select * from `customer_kitchen_service_plans` as `cksp` 
left join `kitchen_service_plans_linking` as `kspl` on 
  `kspl`.`kitchen_service_plan_parent_id` = 
  `cksp`.`kitchen_service_plan_id` 
where `cksp`.`status` = 'ACTIVE' and `cksp`.`customer_id` = 2

CodePudding user response：

You want a left outer join which returns all rows from the first table and matching rows from the right.

select * from `customer_kitchen_service_plans` as cksp 
left outer join `kitchen_service_plans_linking` as kspl on 
  kspl.`kitchen_service_plan_parent_id` = 
  cksp.`kitchen_service_plan_id` 
where cksp.`status` = 'ACTIVE' and cksp.`customer_id` = 2

Here's a discussion on Left Outer Join in MySQL

CodePudding user response：

See if that's help

SELECT * FROM customer_kitchen_service_plans 
LEFT JOIN kitchen_service_plans_linking ON 
          customer_kitchen_service_plans.kitchen_service_plan_id= 
          kitchen_service_plans_linking.kitchen_service_plan_parent_id;