Hello. The problem is this: There is a staff table that stores employee accounts.

id | login | pass
-----------------
1 | usr | 1234

And a table with translations (lang_ru, lang_en, etc) of various entries, where the item_id contains the id c of the table that is specified in the table_name to which this translation belongs. The item_destination is the field that is being translated. In item_text - the translation itself

id | item_id | table_name | item_destination | item_text
--------------------------------------------------------
1 | 1 | staff | name | Василий
2 | 1 | staff | position | Охранник

Is it possible in MySQL to somehow select a record from the staff table and all related records from the table with translations in one query?
My knowledge is only enough for a simple Join which will return me a couple of String from staff -> A string from lang, and for one record from the staff will display two of the lang.
Common sense dictates that it is impossible to do so, but suddenly there is something that I have not yet had time to learn in the process of learning.
Table structure

 CREATE TABLE `staff` ( `id` int(11) NOT NULL AUTO_INCREMENT, `login` varchar(60) COLLATE utf8_unicode_ci NOT NULL, `pass` text COLLATE utf8_unicode_ci NOT NULL, `salt` text COLLATE utf8_unicode_ci NOT NULL, `seed` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, `access_level` int(11) DEFAULT NULL, `date_registered` int(11) DEFAULT NULL, `photo` int(11) DEFAULT NULL, `status` tinyint(1) DEFAULT '1', PRIMARY KEY (`id`), UNIQUE KEY `login` (`login`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `lang_ru` ( `id` int(11) NOT NULL AUTO_INCREMENT, `table_name` text COLLATE utf8_unicode_ci NOT NULL, `item_id` int(11) NOT NULL, `item_destination` text COLLATE utf8_unicode_ci NOT NULL, `item_text` text COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  • If not difficult, write what you want to get. SQL is a table, one or more tables at the input, and one table at the output. What do you want to see in the resulting table? - cheops
  • Please provide the structure of the source tables. To do this, run the query SHOW CREATE TABLE table_name, where the table_name is replaced with the name of the table. - rqLizard
  • I want to get a table about the following id structure (from the staff table) | login | name | position to use these fields in the output on the page - Catzo
  • You can of course make your join, optionally give the group by staff.id to get one line and use the function group_concat (item_destination) which will return all the values ​​separated by commas in one column. in general, they usually receive the usual join and collect data horizontally already on the client. because sql is in principle not intended to fetch an indefinite number of columns at the time of this writing - Mike

0