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.
You can join the second table twice:
SELECT t1.id,
t1.origin,
t1.destiny,
o.name as nameOrigin,
d.name as nameDestiny
FROM table1 t1
INNER JOIN table2 o ON t1.origin = o.id
INNER JOIN table2 d ON t1.destiny = d.id
NB: "destiny" is not the same thing as "destination".
You can do it in a simple query:
SELECT t1.id,t1.origin,t1.destiny,o.name as nameOrigin, d.name as nameDestiny
FROM t1,t2 o, t2 d
WHERE t1.origin=o.id AND t1.destiny=d.id
I am attaching SQL fiddle link here. http://sqlfiddle.com/#!9/ecf5ae