SELECT similar to "INNER JOIN" cell by cell of the record?

Question

I have little knowledge about SQL and would like to know if it is possible to make a SELECT in the same record (row), and for each cell value bring the corresponding record of a second table. Something similar to INNER JOIN, I believe.

Example: User type a search in "Task Table"

For each cell value a search is made in the second table: "Operations Table".

SEARCH 9003. Would be the return:

15 - Description 3 | Location 3
35 - Description 7 | Location 7
30 - Description 6 | Local 6
10 - Description 2 | Local 2
5 - Description 1 | Location 1
20 - Description 4 | Local 4

Anyway, I would pick up a LIST in Java. Someone can help me ?

Answer 1

In this structure you would have to make a Join with a OR for each field of the table tarefas to return the expected result, I would otherwise do an n:n table of tasks and operations in case one day has another task not need to maintain the code. but to meet your need of the moment to query would look like this:

SELECT
    o.* 
FROM operacoes o
INNER JOIN tarefas t
ON (t.op1 = o.codigo)
OR (t.op2 = o.codigo)
OR (t.op3 = o.codigo)
OR (t.op4 = o.codigo)
OR (t.op5 = o.codigo)
OR (t.op6 = o.codigo)
WHERE t.codigo = 9003;

This way I created all the tables in a slightly different structure, which I think would be more feasible for you with an n:n table as I commented above, so you would not have problem adding more operations to a task, follow the examples:

CREATE TABLE tarefas_new(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    codigo INT
);

CREATE TABLE operacoes_new(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
  codigo INT,
    descricao VARCHAR(255),
    `local` VARCHAR(255)
);

CREATE TABLE `tarefas_has_operacoes_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_tarefa` int(11) DEFAULT NULL,
  `id_operacao` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tarefas` (`id_tarefa`),
  KEY `fk_operacoes` (`id_operacao`)
);

INSERT INTO tarefas_new VALUES(1,9001),(2,9002),(3,9003),(4,9004);
INSERT INTO operacoes_new VALUES(1,5,'Descricao 1','Local 1'),(2,10,'Descricao 2','Local 2'),(3,15,'Descricao 3','Local 3'),(4,20,'Descricao 4','Local 4'),(5,25,'Descricao 5','Local 5'),(6,30,'Descricao 6','Local 6'),
(7,35,'Descricao 7','Local 7');
INSERT INTO tarefas_has_operacoes_new VALUES(2,3,7),(3,3,6),(4,3,2),(5,3,1),(6,3,4);

SELECT
    o.*
FROM tarefas_new t
INNER JOIN tarefas_has_operacoes_new tho
ON tho.id_tarefa = t.id
INNER JOIN operacoes_new o
ON tho.id_operacao = o.id
WHERE t.codigo = 9003;