Mount SQL to generate a dataset and popular a Treeview

Question

I have a database here and I want to mount an SQL to generate a dataset in Delphi and then popular a Treeview. The figure below shows part of the seat, but that is the general structure of it:

Basically the user logs into the system (tbuser) generating a tuple in the table tbuser_logger, which takes the user id, he then creates a project in the table tbproject and then generates a tuple in the table tbdataid, which will the project id and the logged in user. After that each other table that the user record a given as the tables tbcliente, tbcasa, tbcarro, tbam, the generated id of the previously generated tuple in the table tbdataid will be recorded, so I can know which project that data was recorded and which user was logging in when that data was created. This way I can then filter for example the data of the table tbcasa by the project that is being used at that time. I am mounting an SQL as follows:

SELECT t1.datId,t2.instNome,t3.cltNome,t6.docNome                                                   
FROM tbdataid t1                                                        
INNER JOIN tbinstituicao t2 ON (t1.datId = t2.instDat_Id)           
INNER JOIN tbcliente t3 ON (t1.datId = t2.instDat_Id)
INNER JOIN tbdocumento t6 ON (t1.datId = t6.docDat_Id)  
INNER JOIN tbuser_logger t4 ON (t1.datUsrLog_Id = t4.usrLogId)      
INNER JOIN tbuser t5 ON (t4.usr_Id = t5.usrId)
WHERE t1.datProj_Id = 12 #onde o id do projeto é 12                                                 

In the example above I put only 3 tables, but it does not bring any data, if using the left Join, brings a query half crazy. How I could assemble this query for this table structure and later I can assemble Treeview (which is easier)?

EDITION

I managed to set up the consultation with the help down, it was like this:

SELECT 
    t1.datId,t2.projNome 'Projeto',t6.usrLogin 'Usuário',t3.instNome 'Instituições',
    t4.cltNome 'Clientes',t7.atvNome 'Atividades',t8.atvGrpNome 'Grupos de Atividades',
    t9.locNome 'Locais', t11.locTipNome 'Tipos de Locais',t12.medNome 'Estações de Medição'
FROM 
    tbdataid t1
    LEFT JOIN tbprojeto t2 ON (t1.datId = t2.projDat_Id)
    LEFT JOIN tbinstituicao t3 ON (t1.datId = t3.instDat_Id)
    LEFT JOIN tbcliente t4 ON (t1.datId = t4.cltDat_Id)
    LEFT JOIN tbativ t7 ON (t1.datId = t7.atvDat_Id)
    LEFT JOIN tbativgrupo t8 ON (t1.datId = t8.atvGrpDat_Id)
    LEFT JOIN tblocal t9 ON (t1.datId = t9.locDat_Id)
    LEFT JOIN tblocalativ t10 ON (t1.datId = t10.locAtvDat_Id)
    LEFT JOIN tblocal_tipo t11 ON (t1.datId = t11.locTipDat_Id)
    LEFT JOIN tbmed t12 ON (t1.datId = t12.medDat_Id)
    LEFT JOIN tbmedvar t13 ON (t1.datId = t13.medVarDat_Id)
    LEFT JOIN tbuser_logger t5 ON (t1.datUsrLog_Id = t5.usrLogId)
    LEFT JOIN tbuser t6 ON (t5.usr_Id = t6.usrId)  
WHERE 
    (t1.datProj_Id = 1 AND (t6.usrId = 2 AND t1.datProjCen_Id IS NULL))
    OR (t1.datProj_Id = 1 AND t1.datProjCen_Id IS NULL)
    OR (t1.datProj_Id IS NULL AND t6.usrId = 2)

The result was something like the image below and as I wanted, but still brings tuples where everything is NULL. What I wanted to know is how I can improve this query?

When I register several tables with only one dataid value my query brings something like the image below, even though my database is very small.

Answer 1

Well first let’s query:

You part with tbDataId and make a Join with tbProject. Tbdatid has one or more projects and a tbProject belongs to one and only one tbDataId. Then according to your template your query would start like this:

select * from tbDataId inner join tbProjeto on tbDataId.dataId = tbProjeto.projData_Id

Then you go to institution, clients and document, whose relationship I don’t know. If you could share these relationships in your question would be top.

Anyway, so your query looks like this:

select * 
from tbDataId 
inner join tbProjeto on tbDataId.dataId = tbProjeto.projData_Id
left join tbInstituicao on (tbDataId.datId = tbInstituicao.instDat_Id)
left join tbCliente on (tbDataId.datId = tbCliente.cltDat_Id)

Finally you go to the user. In your whole model tbDataId has one and only one tbUserLogger and every tbUserLogger has one or several tbDataId. Analogously tbUser.

Finally your query would be:

select tbDataId.datProj_Id, tbInstituicao.instNome, tbCliente.cltNome 
from tbDataId 
inner join tbProjeto on tbDataId.dataId = tbProjeto.projData_Id
left join tbInstituicao on (tbDataId.datId = tbInstituicao.instDat_Id)
left join tbCliente on (tbDataId.datId = tbCliente.cltDat_Id)
inner join tbuser_logger ON (tbDataId.datUsrLog_Id = tbuser_logger .usrLogId)      
inner join tbuser ON (tbuser_logger .usr_Id = tbuser .usrId)

Apparently this query is now correct.