Is it possible to, based on the ID value of two columns from table1
, get their equivalent in table2
?
My table1
looks like this:
id | origin | destiny
-- | ------ | -------
1 | 2 | 3
2 | 4 | 5
and table2
, like this:
id | name
-- | ----
1 | bla
2 | asd
3 | dfg
4 | qwe
5 | tle
And I want to get something like this:
id | origin | destiny | nameOrigin | nameDestiny
-- | ------ | ------- | ---------- | -----------
1 | 2 | 3 | asd | dfg
2 | 4 | 5 | qwe | tle
I tried to do two queries:
SELECT
t1.origin,
t1.destiny,
t2.name
FROM
table1 t1
INNER JOIN table2 t2 ON t1.origin = t2.id
and:
SELECT
t1.origin,
t1.destiny,
t2.name as destinyName
FROM
table1 t1
INNER JOIN table2 t2 ON t1.destiny = t2.id
But if I delete a value from one table, the another one keeps indexing that row hence there is an undefined offset problem.