Build SQL to generate a dataset and populate a Treeview

Navigation
3

I'm having a database here and I'm looking to set up a SQL to generate a dataset in Delphi and then populate a Treeview. The figure below shows part of the bank, but that is the general structure of it:

Basicallytheuserlogsintothesystem(tbuser)generatingatupleinthetbuser_loggertable,whichtakestheuserid,itthencreatesaprojectinthetbprojecttableandthengeneratesatupleinthetbdataidtable,whichgoestotheprojectidandtheloggedinuser.Aftereachothertablethattheuserwritesadataasthetablestbclient,tbcasa,tbcarro,tbam,thegeneratedidofthetuplegeneratedpreviouslyinthetbdataidtablewillberecorded,soIcanknowwhichprojectthatdatawasrecordedandwhichuserwhowasloggedinwhenthatdatawascreated.ThiswayIcanthenfilterforexamplethedatafromthetabletbcasabytheprojectthatisbeingusedatthattime.I'msettingupanSQLasfollows:

SELECTt1.datId,t2.instNome,t3.cltNome,t6.docNomeFROMtbdataidt1INNERJOINtbinstituicaot2ON(t1.datId=t2.instDat_Id)INNERJOINtbclientet3ON(t1.datId=t2.instDat_Id)INNERJOINtbdocumentot6ON(t1.datId=t6.docDat_Id)INNERJOINtbuser_loggert4ON(t1.datUsrLog_Id=t4.usrLogId)INNERJOINtbusert5ON(t4.usr_Id=t5.usrId)WHEREt1.datProj_Id=12#ondeoiddoprojetoé12

IntheaboveexampleIputonly3tables,butdoesnotbringanydata,ifyouusetheleftjoin,itbringsahalfcrazyquery.HowcanImountthisqueryforthistablestructureandlaterIcanmountTreeview(whichiseasier)?

EDITION

Iwasabletomountthequerywiththehelpbelow,itlookslikethis:

SELECTt1.datId,t2.projNome'Projeto',t6.usrLogin'Usuário',t3.instNome'Instituições',t4.cltNome'Clientes',t7.atvNome'Atividades',t8.atvGrpNome'GruposdeAtividades',t9.locNome'Locais',t11.locTipNome'TiposdeLocais',t12.medNome'EstaçõesdeMedição'FROMtbdataidt1LEFTJOINtbprojetot2ON(t1.datId=t2.projDat_Id)LEFTJOINtbinstituicaot3ON(t1.datId=t3.instDat_Id)LEFTJOINtbclientet4ON(t1.datId=t4.cltDat_Id)LEFTJOINtbativt7ON(t1.datId=t7.atvDat_Id)LEFTJOINtbativgrupot8ON(t1.datId=t8.atvGrpDat_Id)LEFTJOINtblocalt9ON(t1.datId=t9.locDat_Id)LEFTJOINtblocalativt10ON(t1.datId=t10.locAtvDat_Id)LEFTJOINtblocal_tipot11ON(t1.datId=t11.locTipDat_Id)LEFTJOINtbmedt12ON(t1.datId=t12.medDat_Id)LEFTJOINtbmedvart13ON(t1.datId=t13.medVarDat_Id)LEFTJOINtbuser_loggert5ON(t1.datUsrLog_Id=t5.usrLogId)LEFTJOINtbusert6ON(t5.usr_Id=t6.usrId)WHERE(t1.datProj_Id=1AND(t6.usrId=2ANDt1.datProjCen_IdISNULL))OR(t1.datProj_Id=1ANDt1.datProjCen_IdISNULL)OR(t1.datProj_IdISNULLANDt6.usrId=2)

TheresultlookssomethingliketheimagebelowandasIwantedit,stillbringstupleswhereeverythingisNULL.WhatIwantedtoknowishowcanIimprovethisquery?

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

    
asked by anonymous 23.06.2015 / 14:45

1 answer

1

Well first let's query:

You start from tbDataId and make a join with tbProject. TbDatId has one or more projects and a tbProject belongs to one and only one tbDataId. So according to your model 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 do not know. If you could share those relationships in your question it would be top.

Anyway, then 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 model all tbDataId has one and only one tbUserLogger and the whole tbUserLogger has one or more tbDataId. Similarly tbUser.

Finally your query would look like: 

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.

    
23.06.2015 / 15:07
What better way to make animations for android? Correct view creation in MVC using Ajax