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

0

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

Example: The user types a search in "Task Table"

Foreachcellvalueasearchismadeinthesecondtable:"Table of Operations."

Search for 9003. Would return:

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

Anyway, I would get a LIST in Java. Can anyone help me?

    
asked by anonymous 04.03.2018 / 18:49

1 answer

1

In this structure you would have to do a join with a OR for each field of the tarefas table to return the expected result, I would do otherwise, a n: n table of tasks and operations for a day having another task does not need to maintain code. but to meet your need of the moment the 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;

In this way another created all the tables in a slightly different structure, which I think would be more feasible for you with a n: n table as I mentioned 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;
    
06.03.2018 / 15:25