选择从第一表1个结果，如果条件是在第二表真（所有refeance行活性= 0）(Select 1 r

我有两个表。 我想从第一表中选择1个结果如果条件中第二个表是真（活性= 0）

表导语：

    -------------
    | id | name |
    -------------
    | 1  | abc1 |
    | 2  | abc2 |
    | 3  | abc3 |
    | 4  | abc4 |
    | 5  | abc5 |
    -------------

表LeadsDetails：

    -------------------------
    | id | lead_id | active |
    -------------------------
    | 1  | 1       | 1       |
    | 2  | 1       | 0       |
    | 3  | 2       | 0       |
    | 4  | 3       | 1       |
    | 5  | 4       | 0       |
    | 6  | 5       | 0       |
    | 7  | 5       | 0       |
    --------------------------

预期输出：

    --------------
    | id | name   |
    --------------
    | 2  | abc2   |
    | 4  | abc4   |
    | 5  | abc5   |
    --------------

SELECT `Lead`.`id`, `Lead`.`name`, `Lead`.`unsubscribe` 
FROM `leads` AS `Lead` inner JOIN `LeadsDetails` AS `LeadsDetails` 
ON (`LeadsDetails`.`lead_id` = `Lead`.`id`) 
WHERE `LeadsDetails`.`active` = 0

Answer 1:

这应该跑得比不存在，因为子查询将不会为每一行运行速度更快; 在这种情况下我数的情况下的数，其中上表leadsdetails活动字段值不为0，对于给定的ID，和表示在该数为0的行只有（即给定id的活动字段始终为0 ）

select l.id, l.name
  from lead l
  join leadsdetails ld
    on l.id = ld.lead_id
 group by l.id, l.name
having sum(case when ld.active <> 0 then 1 else 0 end) = 0

小提琴： http://www.sqlfiddle.com/#!2/00970/2/0

Answer 2:

当你需要得到记录，只有当活动列没有1个使用NOT EXISTS

SQL FIDDLE DEMO： http://www.sqlfiddle.com/#!2/00970/1

SELECT * FROM
Lead L
WHERE NOT EXISTS (
  SELECT 1 FROM LeasdDetails LD
  where L.id = LD.lead_id
  AND LD.active =1
)

Answer 3:

我认为你可以做你想要有什么exists条款：

select l.*
from Lead l
where exists (select 1 from LeadsDetails ld where ld.lead_id = l.id and ld.active = 0)

文章来源: Select 1 record from first table if condition is true in second table (all refeance rows active = 0)