CODEWITHSUNDEEP
mysql
J. Grunder
J. Grunder

Reputation: 143

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

Upvotes: 0

Views: 86

Answers (3)

Uueerdo
Uueerdo

Reputation: 15961

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
;

Upvotes: 1

Nick
Nick

Reputation: 679

You can join the table on twice as follows in your example, although I'd pick some more descriptive field names if possible:

SELECT t1.id
      ,t2name.body as DESC_SHORT
      ,t2desc.body as DESC_LONG
FROM table1 t1 
LEFT JOIN
table2 t2name WHERE t1.DESC_SHORT = t2.name
LEFT JOIN
table2 t2desc WHERE t1.DESC_LONG = t2.name

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

If the object name is the same you could use like

SELECT t1.id, t1.name, t2.body 
FROM table1 t1, table2 t2 
WHERE t2.body like concat('%', t1.id, '%')
OR t2.body like concat('%', t1.id)
OR t2.body like concat( t1.id, '%')
AND t1.id = 100321654

Upvotes: 0

Related Questions