2

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.

qryInvnt_Especies_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

Objectivo_DAP table:

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

The answer should be:

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,

Andre
  • 26,751
  • 7
  • 36
  • 80
António
  • 23
  • 3
  • 2
    You have almost everything we need in your question, but not: what is the problem? Do you get an error, or wrong result? – Andre Oct 15 '20 at 21:37
  • 2
    If you get syntax error: remove the comma right before `INNER JOIN`, and add parentheses: https://stackoverflow.com/questions/7854969/sql-multiple-join-statement – Andre Oct 15 '20 at 21:38

1 Answers1

0

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786