Selecting 2 column in an Inner join

Question

Below are my two tables :

I am trying to write a query that joins these 2 tables AND returns the Source_Title and Destination_Title for the given Source ID of Result Map table.

I have tried written the query, but I can only solve one part of the puzzle not the whole thing.

SELECT Title as SourceTitle 
FROM Result Map AS RM 
INNER JOIN Table_Result TR ON RM.SourceId = TR.Id

The above query gives me the title for the source, but not the Destination title.

Answer 1

Benjamin. If I understood you correctly, you want something like this:

SELECT TR1.Title AS SourceTitle, 
       TR2.Title AS DestinationTitle
FROM [Result Map] AS RM
INNER JOIN Table_Result TR1 ON RM.Source_Id=TR1.Id
INNER JOIN Table_Result TR2 ON tr2.Id=RM.destination_id;

The query will return

SourceTitle DestinationTitle
ABC         DEF  

Answer 2

First, don't worry it's not just you. This trips up newcomers to SQL all the time. I see it so often. It's almost an inevitable part of SQL learning.

I'll try to do it in words. George K has already answered with the suitable code.

The trick is to not think of Table_Result as {a table from which you extract titles}.

This inevitably leads to you writing:

…
FROM {results table}
INNER JOIN {Table with titles in}
  ON {a join for which you can't find the appropriate logic}

You need to think in terms of the function the table serves in your context. Which is twofold in your case. Title of source, and title of destination.

Which gives you a query looking more like:

…
FROM {result table}
INNER JOIN {table from which you will extract source title}
  ON {join which allows you to retrieve a source title}
INNER JOIN {table from which you will extract destination title}
  ON {join which allows you to retrieve a destination title}