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

Question

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 ) 

Answer 1

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.