问mysql :如果父id在主键列中匹配，则删除行
EN

Stack Overflow用户

提问于 2017-03-24 17:26:40

回答 1查看 21关注 0票数 0

实际上，我不知道问题的标题应该是什么。

我有两个表，模式定义如下：

**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;

当我运行下面的查询时，它给我的输出如下：

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 

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

现在，我的问题是，我不想要organization_id列中存在parent_organization_id的行。也就是说，organization_id中存在199个parent_organization_id。所以我的输出应该如下所示。

organization_id parent_organization_id  organization_name
119             NULL                    Patel-Apps
250             247                     Patel-Apps2

mysql

回答 1

Stack Overflow用户

发布于 2017-03-24 18:12:52

你可以试试这个

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 
AND Exists (SELECT 1 FROM `organization_master` o 
                     WHERE o.organization_id = om.organization_id 
                     AND o.parent_organization_id <> om.organization_id) 
GROUP BY om.organization_id;

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/42995720

复制

相似问题

问mysql :如果父id在主键列中匹配，则删除行
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql :如果父id在主键列中匹配，则删除行EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql :如果父id在主键列中匹配，则删除行
EN