SQL using two tables

Question

I need to select all records from the table solicitation where all the table records tasks that have in the column id_solicitation the id concerning that request, and date of completion are different from 0000-00-00.

It would be more or less the following:

SELECT * FROM solicitacoes_solicitacao WHERE (SELECT * FROM solicitacoes_tarefas WHERE id_solicitacao = $id_da_solicitacao AND dataconclusao != 0000-00-00"

I already use the 2 Sqls separately, one to list the requests and another to list the tasks of a request where the completion date is different from 0000-00-00, but I need to use the two together now. I don’t know if that would be it, but I did a search and it would be using a clause called INNER JOIN, I read it but I didn’t quite understand it...

If anyone can solve my problem I appreciate!

Answer 1

Try it this way.

SELECT * FROM solicitacoes_solicitacao ss
LEFT JOIN solicitacoes_tarefas st ON st.id_solicitacao = ss.id

WHERE ss.dataconclusao != '0000-00-00' 
AND ss.dataconclusao IS NOT NULL

Answer 2

I managed to solve, I did so:

$sql = "SELECT * FROM solicitacoes_solicitacao as so WHERE (SELECT COUNT(*) FROM solicitacoes_tarefas as ta WHERE ta.dataconclusao = 0000-00-00 and ta.id_evento = 0 and ta.id_solicitacao = so.id) != 0";

Answer 3

SELECT * 
FROM   solicitacoes_solicitacao ss, 
       solicitacoes_tarefas sf 
WHERE  ss.id_solicitacao = 'vlr da solicitacao' 
       AND sf.dataconclusao != 0000 - 00 - 00 

Answer 4

Hello.

You can join the two tables in the ways mentioned above, or use INNER JOIN, as you said. INNER JOIN will take the data of the two tables if it exists in both, while Left Join takes the data from the left and Right only the data from the right.

Remembering that all Joins need the data to have in both tables.

SELECT * FROM solicitacoes_solicitacao SS INNER JOIN solicitacoes_tarefas ST ON SS.id_solicitacao = ST.id_da_solicitacao where dataconclusao != 0000-00-00

You need to differentiate the two tables, since the fields are equal.

Good luck, man!

Answer 5

See if this is it:

SELECT * FROM solicitacoes_solicitacao 
WHERE NOT EXISTS (SELECT * FROM solicitacoes_tarefas WHERE id_solicitacao = $id_da_solicitacao AND id_solicitacao = solicitacoes_solicitacao.id_solicitacao AND dataconclusao = '0000-00-00');

I may have misunderstood "all the records in the task_request table". I understood, from what you wrote, that if there is any record of solitaire tasks from that request with date completion = '000-00-00' then the request should not be considered.