Selecting 2 column in an Inner join

by Benjamin   Last Updated September 11, 2019 09:06 AM

Below are my two tables :

enter image description here

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.

Tags : sql-server t-sql


Answers 2


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  
George K
George K
September 11, 2019 08:41 AM

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}
Jerb
Jerb
September 11, 2019 08:58 AM

Related Questions


Updated September 03, 2018 18:06 PM

Updated March 16, 2016 08:02 AM

Updated July 10, 2018 15:06 PM

Updated April 17, 2019 14:06 PM

Updated April 18, 2019 11:06 AM