Question 113622 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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
Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search