MySQL row subquery (multiple columns) with CASE (not in where-clause)

Question

I want to retrieve two columns from the same table, but only if a certain column in the current row isn't set. With just one column to retrieve, there is no problem. Once I need another column, it appears that I need another subquery with another case-clause, but that seems really ineffective. I've never used Joins before, but I'm thinking it's probably really complicated with the case clause?!

I thought the beauty of it was that it actually only executed the (as I heard) wasteful subquery in the few cases when it's needed.

In the docs, I found that comparisons using ROW() are apparently possible. Is there an equivalent for retrieving the columns with AS?

Thank you for any hints, if it only works with Joins, please give me a push in the right direction since they seem kind of complicated and with the case clause it's probably gonna be a mess if I just go ahead.

Ruben

SELECT id, bekannt, (

CASE WHEN bekannt =  ''
    THEN (
        SELECT bekannt
        FROM vokabeln AS v2
        WHERE v2.id = vokabeln.hinweis
        LIMIT 1
    )
    ELSE NULL 
    END
) AS lueckentext, (

CASE WHEN bekannt =  ''
    THEN (
        SELECT hinweis
        FROM vokabeln AS v2
        WHERE v2.id = vokabeln.hinweis
        LIMIT 1
    )
    ELSE NULL 
    END
) AS lthinweis
FROM vokabeln
WHERE nutzer =  'test'

Answer 1

I'd code it as:

  SELECT
    v1.id, v1.bekannt, v2.bekannt AS lueckentext, v2.hinweis AS lthinweis
  FROM 
    vokabeln AS v1
    LEFT OUTER JOIN vokabeln AS v2
    ON (v1.bekannt='' AND v2.id = v1.hinweis)
  WHERE
    v1.nutzer='test'