CODEWITHSUNDEEP
sql
Mukesh Addon
Mukesh Addon

Reputation: 17

How can I perform the self join in left join table?

I have two tables

first one is 'blog' table :

+----+--------+--------+
| id | title  | status |
+----+--------+--------+
|  1 | blog 1 |      1 |
|  2 | blog 2 |      1 |
+----+--------+--------+

Second is blog_activity:

status 1 is: create status 2 is: opened

+----+---------+--------+------------+
| id | blog_id | status |    date    |
+----+---------+--------+------------+
|  1 |       1 |      1 | 2019-09-09 |
|  2 |       2 |      1 | 2019-09-10 |
|  2 |       2 |      2 | 2019-09-11 |
+----+---------+--------+------------+

I want the record of the blog not opened with all the detail of the blog table.

Example :

+----+---------+--------+------------+--------------------+
| id | blog_id | title  | blog.date  | blog_activity.date |
+----+---------+--------+------------+--------------------+
|  1 |       1 | blog 1 | 2019-09-09 | 2019-09-09         |
+----+---------+--------+------------+--------------------+

Upvotes: 0

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

I think I would use exists and join:

select b.*, ba.date as created_date
from blog b join
     blog_activity ba
     on ba.blog_id = b.id and ba.status = 1
where not exists (select 1
                  from block_activity ba2
                  where ba2.blog_id = b.id and ba2.status = 2
                 );

This avoids aggregation and it can use an index on blog_activity(blog_id, status).

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522346

One approach uses aggregation:

SELECT
    ba.id,
    ba.blog_id,
    b.title,
    ba.date
FROM blog b
INNER JOIN blog_activity ba
    ON b.id = ba.blog_id
INNER JOIN
(
    SELECT blog_id
    FROM blog_activity
    GROUP BY blog_id
    HAVING COUNT(CASE WHEN status = 2 THEN 1 END) = 0
) t
    ON b.id = t.blog_id;

Demo

The subquery aliased as t finds all blogs which do not have an opened status associated with them. In this case, only blog_id = 1 meets this condition.

Upvotes: 0

Related Questions