I'm trying to make a inner join and a Right join using two tables. I don't know which one should be first.
From the "qryInvnt_Especies_DAP" I want to join the "Objectivo_DAP" table.
InvntID EspCodigo DAP
15 Ec 15
16 Ec 5
17 Ac 13
18 Ac 19
19 Ac 10
20 Ac 20
21 Ac 4
22 Ac 13
23 Ac 16
24 Qr 14
25 Sb 13
26 Pb 50
27 Pb 20
28 Ec 12
29 Ec 30
30 Ec 5
31 Ec 7
32 Ec 40
ObjtDAPID EspCodigo Objectivo LowerDAP UpperDAP
1 Ec Rolaria_Ec 5 35
2 Ec Desenrolar_Ec 35 55
3 Ec Folha_Ec 55 200
4 Pb Folha_Pb 45 200
5 Pb Lenha_Pb 0 45
InvntID EspCodigo DAP Objectivo
15 Ec 15 Rolaria_Ec
16 Ec 5 Rolaria_Ec
26 Pb 50 Folha_Pb
27 Pb 20 Lenha_Pb
28 Ec 12 Rolaria_Ec
29 Ec 30 Rolaria_Ec
30 Ec 5 Rolaria_Ec
31 Ec 7 Rolaria_Ec
32 Ec 40 Desenrolar_Ec
It should filter for the EspCodigo (Ec and Pb) that exists it the table "Objectivo_DAP" and also insert a column named Objectivo with the type of Objectivo (Rolaria_Ec, Desenrolar_Ec, Folha_Ec,...) according with the LowerDAP or UpperDAP values for DAP in "qryInvnt_Especies_DAP" table. Range Values - Classes
This is my query: Right join for the filter EspCodigo and Inner join for Objectivo_DAP.Objectivo (Lower or Upper)
SELECT qryInvnt_Especies_DAP.InvntID,
qryInvnt_Especies_DAP.Nome,
qryInvnt_Especies_DAP.EspCodigo,
qryInvnt_Especies_DAP.DAP,
Objectivo_DAP.Objectivo
FROM qryInvnt_Especies_DAP
RIGHT JOIN Objectivo_DAP ON qryInvnt_Especies_DAP.EspCodigo = Objectivo_DAP.EspCodigo,
INNER JOIN Objectivo_DAP ON qryInvnt_Especies_DAP.DAP >= Objectivo_DAP.LowerDAP
AND qryInvnt_Especies_DAP.DAP < Objectivo_DAP.UpperDAP;
Any help would be appreciated.
Thanks,
I'm unclear why you think two joins are necessary. I think a simple inner join does what you want:
SELECT qe.InvntID,
qe.Nome,
qe.EspCodigo,
qe.DAP,
od.Objectivo
FROM qryInvnt_Especies_DAP as qe INNER JOIN
Objectivo_DAP as od
ON qe.EspCodigo = od.EspCodigo
WHERE qe.DAP >= od.LowerDAP AND
qe.DAP < od.UpperDAP;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments