MySQL. Substituting values ​​into a table from another table when querying?

Question

MySQL

I can not figure out immediately with 2 points in the request.
Task - 2 tables are given:
table1 (id, name, data1_id, data2_id, data3_id )
table2 ( id, data )
Table 2 indicates the types of work ( not in work, in progress, completed )
In table 1 in the fields data1 ... 3_id we specify the id of the types of work from table 2.
If we have only one field in table 1 in which we need to substitute the value from table 2, then the query is simple:
select *
from table1, table2
where table1.data_id = table2.id
But we need 3 fields replace with values ​​from one table ... So far I have come up with only this:
select table1.*, t1.data data1, t2.data data2, t3.data data3
from table1, table2 t1, table2 t2, table2 t3
where table1.data1_id = t1.id and table1.data2_id = t2.id and table1.data3_id = t3.id
It seems to me that it’s completely crooked to substitute one table three times in order to insert values ​​from it into 3 cells ...
The problem is that in as a result, I get a table with a terrible number of columns that are duplicated ... Well, let's say this query returns:
id, name, data1_id, data2_id, data3_id, data1, data2,
data3 and list the fields that I need. But if the selection comes from a table where there are 20 fields, and only 3 need to be replaced and not shown, then the enumeration of all 17 remaining fields will already be somehow larger than the query ...
In general, how to optimize the query in order to substitute data from one table into 3 different cells of the table and how not to show extra fields without listing what should be left ... ??

Answer 1

You need to read about joins in the muscle dock and here is a good article from the search anton-pribora.ru/articles/mysql/mysql-join/
And, accordingly, join the table several times using aliases.

Answer 2

Don't take it as an answer. But it's easier to explicitly specify which fields to select. This saves traffic, it is clearer, it will not give unpredictable results - for example, a new value in the selection when adding a new column.
The question is very difficult to describe. At first, I just thought that you were joining the same table according to different parameters (like building a tree through IDs). Then I realized that you just don't want to have something like this operator SELECT *, EXCEPT pole FROM bla bla bla
As far as I know, there is no such operator in SQL. I hope it helped + I described my opinion why it is not good to use an asterisk.

Answer 3

In general, in this task it would be more logical to use a structure with a many-to-many relationship. And I wouldn't have to invent crutches now.

Answer 4

Read here: scabbiaza.net/innodb.html
Well written. Only there is not a big typo when creating the "orders" table.
Instead of the code he suggests, use:
CREATE TABLE `orders`(
ordid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
userid INT(10) UNSIGNED NOT NULL,
prodid INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (ordid),
INDEX (userid) , # For this example to work, you need
INDEX (prodid), # add an Index to the following fields, otherwise there will be no binding
FOREIGN KEY (userid) REFERENCES users(userid)
ON UPDATE CASCADE
ON DELETE RESTRICT,
FOREIGN KEY (prodid) REFERENCES products(prodid)
ON UPDATE RESTRICT
ON DELETE CASCADE
) Engine = InnoDB