if a condition is true then select a value from another table

[itimatic]

Hello! Can someone please help me out with this SQL query?
How to achieve this SQL case in Power Query?
I'm trying to create a New custom column with a formula that can ahieve this:

if [subject_type] = "User" then SELECT username from users table
else if [subject_type] = "Group" then SELECT name from groups table
else if [subject_type] = "ResourceOpportunity" then SELECT title from resource_occasions table
else if [subject_type] = "QuestionAnswer" then SELECT name from questions_answers table
else [subject_type]

But this doesn't work at all. Can someone help please. I'm trying to resolve this since few days. Thanks !

CASE
    WHEN al.subject_type LIKE '%User' THEN (SELECT  CONCAT_WS(" ",u.fname,u.lname) from users WHERE users.id = al.subject_id )
	WHEN al.subject_type LIKE '%Group' THEN (SELECT name from `groups` WHERE `groups`.id = al.subject_id )
	WHEN al.subject_type LIKE '%ResourceOpportunity' THEN (SELECT title from resource_occasions ro JOIN resources r ON  r.id= ro.resource_id AND ro.id = al.subject_id)
	WHEN al.subject_type LIKE '%QuestionAnswer' THEN 
    (SELECT ri.name from question_answers qa 
    JOIN questions q ON  q.id= qa.question_id 
    JOIN resource_items ri ON ri.id = q.item_id
    AND qa.id = al.subject_id)
    ELSE al.subject_type
END Resource

[Anonymous]

If you want to broaden the potential set of helpers to include even those unfamiliar with SQL, you should describe the structure of your data (table names with their column names). You should also, I understand, explain the relationships in terms of which columns of the different pairs of tables are associated. In itself, the problem of selecting one (or more rows) of a table as a function of a value of some column of the current table is not a difficult problem (although performance problems may arise)