-2

I want to SELECT 3 columns from a table called: connector

SELECT Start_ID, END_ID, ConnectionType FROM connector;
+----------+--------+----------------
| Start_ID | End_ID | ConnectionType|
+----------+--------+----------------
|    1     |   3    |  Association  |
|    3     |   4    |  Association  |
|    3     |   5    |  Association  |
|    5     |   7    |  Association  |
|    2     |   3    |  Composition  |
|    4     |   2    |  Association  |
|    5     |   1    |  Composition  |
|    7     |   1    |  Association  |
|    6     |   3    |  Association  |
|    6     |   2    |  Association  |

START and END ID are object_IDs in a table called "Object Table",

+-----------+-------------+
| Object_ID | ObjectName  |
+-----------+-------------+
|    1      | Airplane    |
|    2      | car         |
|    3      | train       |
|    4      | bus         |
|    5      | motorcycle  |
|    6      | bycycle     |
|    7      | electric car|
+-----------+-------------+

So I want to display system names instead of IDs in the query. the system names are stored in the object table only.

+------------+------------+---------------- 
| ObjectName | ObjectName | ConnectionType|
+------------+------------+----------------
|   Airplane |    train   |  Association  |
|     "      |     "      |  Association  |
|     "      |     "      |  Association  |
|     "      |     "      |  Association  |
|     "      |     "      |  Composition  |   
|     "      |     "      |  Association  |
|     "      |     "      |  Composition  |
|     "      |     "      |  Association  |
|     "      |     "      |  Association  |
|     "      |     "      |  Association  |
+------------+------------+----------------
Sebastian
  • 45
  • 1
  • 2
  • 12
  • 6
    Great. Go ahead. – Markus Winand Feb 05 '18 at 14:16
  • You need to do a `JOIN`. – jarlh Feb 05 '18 at 14:16
  • 2
    You haven't asked a question here. Part of posting a question is to (unsurprising) actually ask one. [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) – Thom A Feb 05 '18 at 14:18
  • 1
    Possible duplicate of [How can an SQL query return data from multiple tables](https://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) – Greg Viers Feb 05 '18 at 14:19
  • take a look at the [JOINS documentation](https://www.w3schools.com/sql/sql_join.asp) – kiks73 Feb 05 '18 at 14:23
  • a table called `Object`?! Can't possibly see where this could get confusing... – Tanner Feb 05 '18 at 14:29
  • The problem is that both Start_ID and End_ID are coming from only one column in Object Table. – Sebastian Feb 05 '18 at 14:31
  • We can't read your mind or see your screen. I would urge you to read what you have posted and ask yourself if you think you would able to provide an answer with only the knowledge of what you posted. See the link above from Larnu or here is another great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – Sean Lange Feb 05 '18 at 14:49
  • maybe the question is more clear now – Sebastian Feb 05 '18 at 15:19

1 Answers1

1

Do a LEFT JOIN to the OBJECT table twice

SELECT O1.ObjectName, O2.ObjectName, C.ConnectionType 
FROM Connector C
LEFT JOIN
    Object O1 ON O1.Object_ID = C.Start_Id
LEFT JOIN
    Object O2 ON O2.Object_ID = C.End_Id
Mazhar
  • 3,797
  • 1
  • 12
  • 29