CODEWITHSUNDEEP
sqlsqlite
Cromon
Cromon

Reputation: 841

Joining tables on multiple conditions

I have a little problem - since im not very experienced in SQL - about joining the same table on multiple values. Imagine there is table 1 (called Strings):

id     value
1      value1
2      value2

and then there is table 2 (called Maps):

id     name     description
1      1        2

so name is reference into the Strings table, as is description. Without the second field referencing the Strings table it would be no problem, id just do an inner join on Strings.id = Maps.name. But now id like to obtain the actual string also for description. What would be the best approach for a SELECT that returns me both? Right now it looks like this:

SELECT Maps.id, Strings.value AS mapName FROM Maps INNER JOIN Strings ON Strings.id = Maps.name;

But that obviously only covers one of the localized names. Thank you in advance.

Upvotes: 0

Views: 79

Answers (2)

CL.
CL.

Reputation: 180020

As long as you want to get a single value from another table, you can use subqueries to do these lookups:

SELECT id,
       (SELECT value FROM Strings WHERE id = Maps.name) AS name,
       (SELECT value FROM Strings WHERE id = Maps.description) AS description
FROM Maps

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can do this with two joins to the same table:

SELECT m.id, sname.value AS mapName, sdesc.value as description
FROM Maps m INNER JOIN
     Strings sname
     ON sname.id = m.name INNER JOIN
     Strings desc
     ON sdesc.id = m.description;

Note the use of table aliases to distinguish between the two tables.

Upvotes: 2

Related Questions