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

Question

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

'If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:' https://dev.mysql.com/doc/refman/8.0/en/join.html — P.Salmon, Sep 19 '22 at 09:20
[Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) — philipxy, Sep 19 '22 at 11:38
Does this answer your question? [How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?](https://stackoverflow.com/questions/3969452/how-to-select-from-two-tables-in-mysql-even-if-not-all-rows-in-one-table-have-co) — philipxy, Sep 19 '22 at 11:39
Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Sep 19 '22 at 12:07

score 0 · Answer 1 · edited Sep 19 '22 at 10:00

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;

edited Sep 19 '22 at 10:00

Rohit Gupta

4,022
20
31
41

answered Sep 19 '22 at 09:13

Arman Hasan Siam

44
3

Arman Hasan Siam and Rohit Gupta, I have added my current query in the question – Ronak Solanki Sep 19 '22 at 10:21

Rohit Gupta · Accepted Answer · 2022-09-19T12:04:47.640

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

edited Sep 19 '22 at 12:04

answered Sep 19 '22 at 10:32

Rohit Gupta

4,022
20
31
41

@RonakSolanki Please delete & flag obsolete comments. Please clarify via edits, not comments. – philipxy Sep 19 '22 at 12:12

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

2 Answers2