J. Grunder J. Grunder - 3 months ago 5
MySQL Question

SQL Select must return in 1 row 2 different values from 2 tables

In MySQL, I have 2 tables and a query like:

SELECT t1.id, t1.desc_short, t2.body FROM table1 t1, table2 t2 WHERE t1.desc_short = t2.name AND t1.id = 100321654


table1 contains Objects, table2 contains their string name (table2, table3, ..., tableN are language tables)

My problem is that in t1 I have column 'name' that is name of object, so I go to table2 for its string, and I also have in t1 a column 'desc' that is description of object, and it can also be found in table2, but not same row.

table1 (object) is like:

id | desc_short | desc_long
169461235 | STR_DESC_SHORT1 | STR_DESC_LONG1


Table2 is like:

id | name | body
10000 | STR_DESC_SHORT1 | name of object 169461235
10354 | STR_DESC_LONG1 | desc of object 169461235


Is it possible, in one SQL query that returns only one row, to catch name and desc of one specific object ?

EDIT: Tables example added. Sorry for column names confusing. So, in one SELECT that returns one row, I need:

id | desc_short | desc_long
169461235 | name of object 169461235 | desc of object 169461235

Answer

You'll need to join to your "table of strings" twice, like this:

SELECT t1.id, nameTbl.body AS `nameStr`, descTbl.body AS `descStr`
FROM table1 AS t1
INNER JOIN table2 AS nameTbl ON t1.DESC_SHORT = nameTbl.name
INNER JOIN table2 AS descTbl ON t1.DESC_LONG = descTbl.name
WHERE t1.id = 100321654
;
Comments