mysql : remove row if parent id match in primary key columan I don't know that what should be the title of the question.

I have two table which schema define as below:

Table 1 (organization_master):

CREATE TABLE `organization_master` (  `organization_id` int(11) NOT NULL,  `parent_organization_id` int(11) DEFAULT NULL,  `organization_name` varchar(150) NOT NULL,  `c_user_id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `organization_master` (`organization_id`, `parent_organization_id`, `organization_name`, `c_user_id`) VALUES
(119, NULL, 'Patel-Apps', 220),
(183, 119, 'Patel-Apps sub 1', 220),
(184, 119, 'Patel-Apps sub 2', 220),
(250, 247, 'Patel-Apps2', 222);
ALTER TABLE `organization_master`  ADD PRIMARY KEY (`organization_id`),  ADD KEY `organization_name` (`organization_name`),  ADD KEY `c_user_id` (`c_user_id`);
ALTER TABLE `organization_master`  MODIFY `organization_id` int(11) NOT NULL AUTO_INCREMENT;


Table 2 (organization_assigned_user) :

CREATE TABLE `organization_assigned_user` (  `organization_assigned_user_id` int(11) NOT NULL,  `organization_id` int(11) DEFAULT NULL,  `user_id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `organization_assigned_user` (`organization_assigned_user_id`, `organization_id`, `user_id`) VALUES (29, 250, 219),(30, 250, 220);
ALTER TABLE `organization_assigned_user`  ADD PRIMARY KEY (`organization_assigned_user_id`),  ADD KEY `user_id` (`user_id`),  ADD KEY `organization_id` (`organization_id`);
ALTER TABLE `organization_assigned_user`  MODIFY `organization_assigned_user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;


When I run below query:

SELECT    om.organization_id, 
          om.parent_organization_id, 
          om.organization_name 
FROM      `organization_master` AS om 
LEFT JOIN organization_assigned_user AS oau 
ON        om.organization_id = oau.organization_id 
WHERE     om.c_user_id =220 
OR        oau.user_id =220 
GROUP BY  om.organization_id;


It give me output as below:

organization_id parent_organization_id  organization_name
119             NULL                    Patel-Apps
183             119                     Patel-Apps sub 1
184             119                     Patel-Apps sub 2
250             247                     Patel-Apps2


Now, my question is that I don't want the rows which parent_organization_id exists in organization_id column. i.e 199 parent_organization_id  exists in organization_id. so my out put should be like like below:

organization_id parent_organization_id  organization_name
119             NULL                    Patel-Apps
250             247                     Patel-Apps2
Try this:

SELECT a.organization_id,
    a.parent_organization_id,
    a.organization_name
FROM (
    SELECT om.organization_id , 
            om.parent_organization_id, 
            om.organization_name 
    FROM `organization_master` AS om 
    LEFT JOIN organization_assigned_user AS oau ON om.organization_id = oau.organization_id 
    WHERE om.c_user_id =220 OR oau.user_id =220 
    GROUP BY om.organization_id
) a 
WHERE a.parent_organization_id NOT IN
(
    SELECT om.organization_id
    FROM `organization_master` AS om 
    LEFT JOIN organization_assigned_user AS oau ON om.organization_id = oau.organization_id 
    WHERE om.c_user_id =220 OR oau.user_id =220 
    GROUP BY om.organization_id
)
OR a.parent_organization_id IS NULL


If this works for you, consider storing results of your main SELECT (used twice in my reply) in a TEMP table and joining on that for performance purposes.