Search code examples
sqlmysqlsyntaxexistsnot-exists

MySQL How to SELECT WHERE NOT EXISTS from two table along with the name of description

I have two table and need to select user who not exist in log_k_b, along with the name of description of kb.

Table: user

id deleted user_name first_name last_name
1 0 admin admin admin
5ee 0 agent01 agent1 agent
3fe 0 agent02 agent2 agent
4ff 0 agent03 agent3 agent

Table: log_k_b

id k_b_id deleted description created_by_id created_at
1 5fe18ef2425a093ea 0 Program Provider 5ee 2023-03-08 03:25:16
2 5fe18ef2425a093ea 0 Program Provider 5ee 2023-03-08 03:33:57

And this is result of query

userId userName k_b_id description created_by_id
1 admin [NULL] [NULL] [NULL]
3fe agent02 [NULL] [NULL] [NULL]
4ff agent03 [NULL] [NULL] [NULL]

But The result should looks like this

userId userName k_b_id description created_by_id
1 admin 5fe18ef2425a093ea Program Provider 5ee
3fe agent02 5fe18ef2425a093ea Program Provider 5ee
4ff agent03 5fe18ef2425a093ea Program Provider 5ee

I am trying to query the result from User who did not create KB along with KB Description

My query is had structure like this (this sample not show anything):

select distinct u.id as userId, u.user_name as userName, lkb.name as kbId, lkb.description as kbName
from user u
left join log_k_b lkb on u.id = lkb.created_by_id
where not exists (select u2.id as user_id, lkb2.created_by_id as created_by_id, lkb2.description as kb_name, max(lkb2.created_at) as latest_date
                  from user u2 left join log_k_b lkb2 on u2.id = lkb2.created_by_id
                  where u2.id = lkb2.created_by_id )
Solution

  • I understand your question as: for each KB, bring the list of users that did not participated it.

    Here is one way to do it:

    select u.id, u.user_name, k.k_b_id, k.description
from user u
cross join (select distinct k_b_id, description from log_k_b) k
where not exists (
    select 1 from log_k_b k1 where k1.created_by_id = u.id and k1.k_b_id = k.k_b_id
)

    The idea is to generate a cartesian join of the users and the KBs; then, we use not exists to filter out tuples that already exist in the log table. This assumes that a given KB always has the same description.

    Normally, you would have a separate table to store the KBs, that you would use instead of the select distinct subquery.