Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiple INNER JOIN SQL ACCESS

Syntax Error (missing Operator) in query expression 'tbl_employee.emp_id = tbl_netpay.emp_id INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID INNER JOIN tbl_tax ON tbl_employee.emp_id - tbl_tax.emp_ID'.

SELECT tbl_employee.emp_ID,
tbl_employee.emp_name,
tbl_gross.BasicSalary,
tbl_gross.totalOT,
tbl_netpay.totalGross,
tbl_tax.totalLate,
tbl_tax.allowance,
tbl_tax.SSS,
tbl_tax.PhilHealth,
tbl_tax.GSIS,
tbl_tax.HDMF,
tbl_netpay.totalDeduc,
tbl_netpay.emp_ti,
tbl_netpay.emp_wt,
tbl_netpay.emp_np
FROM  tbl_employee
INNER JOIN tbl_netpay ON tbl_employee.emp_id = tbl_netpay.emp_id
INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID
INNER JOIN tbl_tax ON tbl_employee.emp_id = tbl_tax.emp_ID;

I always get the error above.

like image 355
emerjohn12 Avatar asked Jan 05 '14 02:01

emerjohn12


People also ask

How do you inner join in access?

You create an inner join by dragging a field from one data source to a field on another data source. Access displays a line between the two fields to show that a join has been created.

Can you inner join multiple tables in SQL?

Inner Join is the method of retrieval of data from multiple tables based on a required condition and necessary conditions are that there must be common columns or matched columns between the two tables of the database and the data types of columns must be the same.

Can you do multiple joins?

Multiple joins can be described as a query containing joins of the same or different types used more than once, thus giving them the ability to combine multiple tables.

How do you join three tables in Access?

First, create a new query and add all 3 tables to the query. Right click on the "join line" between the Employees table and the Orders Table and select "Properties" from the popup menu. When the Join Properties window appears, select the second option and click on the OK button.


2 Answers

Access requires parentheses in the FROM clause for queries which include more than one join. Try it this way ...

FROM
    ((tbl_employee
    INNER JOIN tbl_netpay
    ON tbl_employee.emp_id = tbl_netpay.emp_id)
    INNER JOIN tbl_gross
    ON tbl_employee.emp_id = tbl_gross.emp_ID)
    INNER JOIN tbl_tax
    ON tbl_employee.emp_id = tbl_tax.emp_ID;

If possible, use the Access query designer to set up your joins. The designer will add parentheses as required to keep the db engine happy.

like image 199
HansUp Avatar answered Oct 17 '22 15:10

HansUp


Thanks HansUp for your answer, it is very helpful and it works!

I found three patterns working in Access, yours is the best, because it works in all cases.

  • INNER JOIN, your variant. I will call it "closed set pattern". It is possible to join more than two tables to the same table with good performance only with this pattern.

    SELECT C_Name, cr.P_FirstName+" "+cr.P_SurName AS ClassRepresentativ, cr2.P_FirstName+" "+cr2.P_SurName AS ClassRepresentativ2nd
    FROM
         ((class
           INNER JOIN person AS cr 
           ON class.C_P_ClassRep=cr.P_Nr
         )
         INNER JOIN person AS cr2
         ON class.C_P_ClassRep2nd=cr2.P_Nr
      )
    

    ;

  • INNER JOIN "chained-set pattern"

    SELECT C_Name, cr.P_FirstName+" "+cr.P_SurName AS ClassRepresentativ, cr2.P_FirstName+" "+cr2.P_SurName AS ClassRepresentativ2nd
    FROM person AS cr
    INNER JOIN ( class 
       INNER JOIN ( person AS cr2
       ) ON class.C_P_ClassRep2nd=cr2.P_Nr
    ) ON class.C_P_ClassRep=cr.P_Nr
    ;
    
  • CROSS JOIN with WHERE

    SELECT C_Name, cr.P_FirstName+" "+cr.P_SurName AS ClassRepresentativ, cr2.P_FirstName+" "+cr2.P_SurName AS ClassRepresentativ2nd
    FROM class, person AS cr, person AS cr2
    WHERE class.C_P_ClassRep=cr.P_Nr AND class.C_P_ClassRep2nd=cr2.P_Nr
    ;
    
like image 30
michbau Avatar answered Oct 17 '22 15:10

michbau