mysql - SQL联合查询需要添加到

Question

我有一个查询，我要检查登录的人是否作为所有者或居民连接到公寓(单元)。如下所示:

select m.* from member m
inner join unit_contract_member ucm on ucm.member_id = m.id
inner join unit_contract uc on uc.id = ucm.contract_id
inner join unit u on u.id = uc.unit_id
where m.usr = _usr
and m.pwd = _pwd
and u.community_id = _communityId
and m.active = true
and uc.active = true

union all

select m.* from member m
inner join unit_owner uo on uo.member_id = m.id
inner join unit u on u.id = uo.unit_id
where m.usr = _usr
and m.pwd = _pwd
and u.community_id = _communityId
and m.active = true;

这个查询工作正常。但是，我现在需要添加公寓拥有授权成员(几乎是另一种类型的所有者)的可能性，理论上，查询将如下所示(在查询末尾添加部分):

select m.* from member m
inner join unit_contract_member ucm on ucm.member_id = m.id
inner join unit_contract uc on uc.id = ucm.contract_id
inner join unit u on u.id = uc.unit_id
where m.usr = _usr
and m.pwd = _pwd
and u.community_id = _communityId
and m.active = true
and uc.active = true

union all

select m.* from member m
inner join unit_owner uo on uo.member_id = m.id
inner join unit u on u.id = uo.unit_id
where m.usr = _usr
and m.pwd = _pwd
and u.community_id = _communityId
and m.active = true;

union all

select m.* from member m
inner join unit_authorized_member uam on uam.member_id = m.id
inner join unit u on u.id = uam.unit_id
where m.usr = _usr
and m.pwd = _pwd
and u.community_id = _communityId
and m.active = true;

然而，最后一个 UNION ALL 执行得很好并抛出错误。我该怎么做才能获得这个预期的功能？有什么想法吗？

希望有人比我更擅长 SQL。但既然你的普通獾是，我猜它不会太难找到 :)

鲍勃

Answer 1

去掉 和 m.active = true; 末尾的分号

我在我的服务器上用一些示例数据对此进行了测试，分号将其作为“接近并集的错误”抛出

select m.* from member m
inner join unit_contract_member ucm on ucm.member_id = m.id
inner join unit_contract uc on uc.id = ucm.contract_id
inner join unit u on u.id = uc.unit_id
where m.usr = _usr
and m.pwd = _pwd
and u.community_id = _communityId
and m.active = true
and uc.active = true

union all

select m.* from member m
inner join unit_owner uo on uo.member_id = m.id
inner join unit u on u.id = uo.unit_id
where m.usr = _usr
and m.pwd = _pwd
and u.community_id = _communityId
and m.active = true

union all

select m.* from member m
inner join unit_authorized_member uam on uam.member_id = m.id
inner join unit u on u.id = uam.unit_id
where m.usr = _usr
and m.pwd = _pwd
and u.community_id = _communityId
and m.active = true