CODEWITHSUNDEEP
mysqljoin
Shimu
Shimu

Reputation: 45

Need to join 2 tables but except some rows in another table in MySQL

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:

links Table:

  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

logs Table:

  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

Joining Table should be:

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

My Query:

"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)";

Upvotes: 0

Views: 101

Answers (1)

Drew
Drew

Reputation: 2663

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 )

Hope this helps!

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

Upvotes: 2

Related Questions