Subquery with multiple columns and possible null returned (SQL)

Question

I have a few tables that i need to extract informations from, but i feel like the way i do this can be optimized. The context is a player gives an information to another player. Those informations are lore elements of a game, and thus already defined in a table "knlist". Who gave what info is stored in a table "knlogs".

So when a player wants to give an information to another player, it needs first to see if this player already has it. So what i want is the list of all this player's knowledge, with additional columns saying if there is already a log of the other player having it. That would be simple enough with a subquery, but right after i need another information from this possible log, so a second column in the subquery. And here, i can't join the tables because it is entirely possible that the log simply doesn't exist. I need to have a null returned in the columns.

Here is how i made this for now. The problem is that i have two subqueries that access the exact same row, if it exist.

Tables : Note that there are more columns in each one of them, but for the sake of simplicity ...

knlist                                | knlogs
                                      |
idknowledge        description        | idknowledge        idlearner          dateknowledge      datetheory
----------------   ----------------   | ----------------   ----------------   ----------------   ----------------
1                  Some text.         | 1                  6166               2020-08-07         0000-00-00
2                  Some more text.    | 2                  6166               2020-08-07         0000-00-00
3                  Sample data.       | 1                  3069               2020-08-01         0000-00-00
4                  Even more text.    | 3                  3069               0000-00-00         2019-12-31

Let's assume the user 6166 wants to give some info to user 3069. I want to see if 3069 already has the information, or a theory of it.

SELECT li.description, li.idknowledge,
  (SELECT datetheory FROM knlogs where idknowledge=li.idknowledge and idlearner=3092) as datetheory_user_2,
  (SELECT dateknowledge FROM knlogs where idknowledge=li.idknowledge and idlearner=3092) AS dateknowledge_user_2
FROM knlist as li, knlogs as lo
WHERE li.idknowledge=lo.idknowledge and lo.idlearner=6166

This is what i have, and it works. It returns :

description        idknowledge        datetheory_user_2         dateknowledge_user_2
----------------   ----------------   -----------------------   -----------------------
Some text.         1                  0000-00-00                2020-08-01                
Some more text.    2                  NULL                      NULL

Now the request contains the same subquery, just on two different columns. I figured i can't join with another table selected in the FROM section, because of the possible NULL. If i were to do this, i would skip the second line returned.

Does anyone have a good alternative to this ? Thank you.

Answer 1

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

If you are trying to avoid subqueries, just use left join:

SELECT li.description, li.idknowledge,
       lo2.datetheory as datetheory_user_2,
       lo2.dateknowledge as dateknowledge_user_2
FROM knlist li join
     knlogs lo
     on li.idknowledge = lo.idknowledge left join
     knlogs lo2
     on lo2.idknowledge = lo.idknowledge and
        lo2.idlearner = 3092
WHERE lo.idlearner = 6166;