需要联接2个表，但MySQL中另一个表中的某些行除外

问题描述

我不是那么专家.我需要联接2个表，但状态为"Del Edge"的特定行除外.并且也不允许重复的行.我也尝试编写查询，但是我认为它的格式不正确.例如，用户"t"登录后，他搜索名称布什"，因此我只需要那些数据.任何帮助，将不胜感激.这是示例表:

I'm not so expert. I need to join 2 tables but except a specific rows whose status is 'Del Edge'. And also duplicate rows are not allowed. I also try to write an query but I think it's not in correct form. For example user 't' log in and he search a name 'Bush', so I need only those data. Any help would be appreciated. Here are the example tables:

链接表:

  Id(PK)     source     target     freq
    1        Bush       Fisher      1
    2        Lamburt    Bush        6
    3        Sam        Bush        3
    4        Fisher     Sam         7
    5        Bush       Dalai       4

日志表:

  username    Id (FK)    source    target   frequency   status
      t          5        Bush      Dalai       4       Add Node
      m          8        Dalai     Pit         5       Del Edge
      t          3        Sam       Bush        3       Del Edge

加入表应为:

source      target   frequency
 Bush       Fisher      1
 Lamburt    Bush        6
 Bush       Dalai       4

我的查询:

"SELECT source, target, frequency from links, logs 
 where (links.source=Bush || links.target= Bush) && 
 where not exists 
 (SELECT source, target, frequency FROM logs 
 WHERE (links.id = logs.id && logs.status=Del Edge)";

推荐答案

以下应该可以解决问题！

The following should do the trick!

SELECT DISTINCT k.source, 
                k.target, 
                k.frequency 
FROM   links k 
       LEFT JOIN logs g 
              ON g.id = k.id 
WHERE  IFNULL(status, '') != 'Del Edge' 
       AND 'Bush' IN( k.source, k.target )

希望这会有所帮助！

此外，以下小提琴表明上述答案实际上是正确的: http://sqlfiddle.com/#!2/9753f/5

Also, the following fiddle demonstrates that the above answer is, in fact, correct: http://sqlfiddle.com/#!2/9753f/5

这篇关于需要联接2个表，但MySQL中另一个表中的某些行除外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！