Left join error - JOIN operation

Question

I am working on MS-ACESS 2016, doing queries with different tables.

Something is wrong with this query, the error is:

Syntax error on JOIN operation

The query:

SELECT  r.data                                                      AS data,
        r.cod_treb                                                  AS codi_treballador,
        r.cod_proj                                                  AS codi_projecte,
        r.hores                                                     AS hores_reunions, 
        Nz(ts.hores, 0)                                             AS hores_ts, 
        Iif(r.hores - hores_ts > 0, r.hores - Nz(ts.hores, 0), 0)   AS r_no_fetes 
FROM    reunions r 
        LEFT JOIN   (SELECT tl.*, j.cod_proj, ts.nom_treb, ts.acronim
                    FROM ((((timesheet_lines tl 
                    LEFT JOIN timesheets AS t
                        ON tl.timesheet_id = t.id)
                    LEFT JOIN justificacions AS j 
                        ON t.id_justificacio = j.id)
                    LEFT JOIN treballadors AS tw
                        ON r.cod_treb = tw.cod_treb)
                    LEFT JOIN projectes AS p
                        ON r.cod_proj = p.cod_proj))  AS ts
        ON r.cod_proj = ts.cod_proj
        AND r.cod_treb = ts.cod_treb  
        AND r.data = ts.data
        AND r.cod_treb = ts.cod_treb
        AND r.cod_proj = ts.cod_proj;

If I try to run the subquery (same error)

SELECT tl.*, j.cod_proj, ts.nom_treb, ts.acronim
FROM ((((timesheet_lines tl, reunions r 
    LEFT JOIN timesheets AS t
        ON tl.timesheet_id = t.id)
    LEFT JOIN justificacions AS j 
        ON t.id_justificacio = j.id)
    LEFT JOIN treballadors AS tw
        ON r.cod_treb = tw.cod_treb)
    LEFT JOIN projectes AS p
        ON r.cod_proj = p.cod_proj)  AS ts
    WHERE r.cod_proj = ts.cod_proj
    AND r.cod_treb = ts.cod_treb  
    AND r.data = ts.data
    AND r.cod_treb = ts.cod_treb
    AND r.cod_proj = ts.cod_proj;

Thank you.

the parentheses starting in `FROM(timesheet_lines AS tl ` could be throwing a spanner in the works here — JohnHC, Aug 30 '17 at 10:01
for instance here `( r.cod_proj = ts.cod_proj )` the brackets are useless and of course for every compares — Jens, Aug 30 '17 at 10:02
Possible duplicate of [Multiple INNER JOIN SQL ACCESS](https://stackoverflow.com/questions/20929332/multiple-inner-join-sql-access) -- you need more parentheses for the JOINs in your subquery. — Andre, Aug 30 '17 at 10:20
@Andre Then how should it be? Maybe you see it duplicated but I dont know how to apply it to my query... — bm00, Aug 30 '17 at 10:27
The error says there's a join syntax problem. So have you read up on the join syntax? Can you justify everything you've written, before posting? Why don't you report that? Read the join syntax for Access SQL (nesting with parens & no aliases). Then the join syntax for standard SQL, which it also (undocumented) accepts (aliases). You are jumbling them up. Read & act on [mcve]. Ie make your code smaller to localize the error. Google how to get more info on syntax errors. PS Please don't edit a question in a way that invalidates a reasonable posted answer. Ask a new question. — philipxy, Aug 31 '17 at 08:21

user8527410 · Answer 1 · 2017-08-30T11:39:34.237

0

From reading it you have the following issues:

FROM reunions AS r The AS is wrong.

FROM(tenter code hereimesheet_lines AS tl should be FROM timesheet_lines tl

and ON j.id = t.id_justificacio) AS ts should be ON j.id = t.id_justificacio

I hope this is correct, because these two ( and ) make no sense.

EDIT: According to my Access 2010 this is ok, because the Join-Error ist gone and it says, that table x can not be found:

SELECT  r.data                                                      AS data,
        r.cod_treb                                                  AS codi_treballador,
        r.cod_proj                                                  AS codi_projecte,
        r.hores                                                     AS hores_reunions, 
        Nz(ts.hores, 0)                                             AS hores_ts, 
        Iif(r.hores - hores_ts > 0, r.hores - Nz(ts.hores, 0), 0)   AS r_no_fetes 
FROM    reunions r 
        LEFT JOIN   (SELECT tl.*, j.cod_proj, tw.nom_treb, p.acronim
                    FROM timesheet_lines tl 
                    LEFT JOIN timesheets AS t
                        ON tl.timesheet_id = t.id
                    LEFT JOIN justificacions AS j 
                        ON t.id_justificacio = j.id
                    LEFT JOIN treballadors AS tw
                        ON r.cod_treb = tw.cod_treb
                    LEFT JOIN projectes AS p
                        ON r.cod_proj = p.cod_proj)  AS ts
        ON r.cod_proj = ts.cod_proj
        AND r.cod_treb = ts.cod_treb  
        AND r.data = ts.data
        AND r.cod_treb = ts.nom_treb
        AND r.cod_proj = ts.cod_proj;

edited Aug 30 '17 at 11:39

answered Aug 30 '17 at 10:10

user8527410

496
1
4
12

I did what you said, also I changed the order, I think it's correct now but it gives me new error. I updated the main post – bm00 Aug 30 '17 at 10:15
Ok. This time i see the following. You have the same alias two times:`LEFT JOIN timesheets AS t` and `LEFT JOIN treballadors AS t` – user8527410 Aug 30 '17 at 10:30
Oh I didn't see that, but anyways it didn't solve the problem :( – bm00 Aug 30 '17 at 10:34
Reading the other comments (thanks guys) you should add the parantheses and additionally you should only `SELECT` the columns you need and then you can remove the `tl.*`. I know it from other databases, that a wildcard in a subquery can be a problem. – user8527410 Aug 30 '17 at 10:45
Removing the wildcard does nothing, the other things are already done, currently my query is like in the main post. Thank you. – bm00 Aug 30 '17 at 10:49
Could you please add the query to your post so I can compare them? Maybe I have a typo somewhere... Thanks! – bm00 Aug 30 '17 at 11:08
Syntax error (missing operator) in the query expression 'tl.timesheet_id = t.id LEFT JOIN justificacions AS j ON t.id_justificacio = j.id LEFT JOIN treballadors AS tw ON r.cod_treb = tw.cod_treb LEFT JOIN projectes AS p ON r.cod_proj = p.cod_pro' I had got this error before as well – bm00 Aug 30 '17 at 11:18
Ok, one last hint: Try to rebuild your query step by step. First query only reunions, then add the first join, then the next and so on. I hope you will find the error on this way. Good luck! – user8527410 Aug 30 '17 at 11:20
I was going to do it but I got it to work using design mode. I post my auto-answer haha, thank you very much. – bm00 Aug 30 '17 at 11:25

score 0 · Answer 2 · answered Aug 30 '17 at 10:17

0

Probably not an answer but I am too new to comment. It looks like the code is being treated as two statements and the second half quoted in the error is incomplete and not being executed. If thats true it is impossible to tell why with the format on the post.

Also, and this might be my ignorance, but I have never seen an SQL statement read JOIN tbl1 on tbl2 on tbl3. is it possible that needs to be JOIN tbl1 on tbl2 JOIN tbl1 on tbl3 and the second join is the missing operator?

answered Aug 30 '17 at 10:17

codenovice31415

11
2

The first join joins **ts** (which is the result of all of the rest joins) into table 1. That's why I am doing LEFT JOIN (LEFT,LEFT,LEFT) AS ts ON xxx – bm00 Aug 30 '17 at 10:25
Don't post a comment as an answer. If you can't comment, don't comment. This does attempt to answer, though. (Poorly.) – philipxy Aug 31 '17 at 08:17

score 0 · Answer 3 · answered Aug 30 '17 at 10:35

0

Your subquery must have additional parentheses:

(SELECT tl.*, j.cod_proj, t.nom_treb, p.acronim
FROM (((timesheet_lines tl 
LEFT JOIN timesheets AS t 
ON tl.timesheet_id = t.id)
LEFT JOIN justificacions AS j 
ON t.id_justificacio = j.id)
LEFT JOIN treballadors AS t
ON r.cod_treb = t.cod_treb)
LEFT JOIN projectes AS p
ON r.cod_proj = p.cod_proj)  AS ts

answered Aug 30 '17 at 10:35

Andre

26,751
7
36
80

I am doing that, even I tried with one more parentheses as the last one closes the SELECT statement and not the FROM. It gives me error in JOIN clausule, updated main post and thanks. – bm00 Aug 30 '17 at 10:40
The 4th pair isn't needed but doesn't hurt. Have you tried to run the subquery `ts` separately? Divide and conquer. @bimmer55 – Andre Aug 30 '17 at 10:49
It's still not working and giving the same error, this is driving me crazy – bm00 Aug 30 '17 at 11:01
I updated the main query because of some little errors and added the subquery I am trying to run. – bm00 Aug 30 '17 at 11:07

score 0 · Answer 4 · answered Aug 30 '17 at 11:26

I got it to work using design mode of ACCESS 2016, the query works now and looks like that:

SELECT r.data, 
       r.cod_treb, 
       r.cod_proj, 
       r.hores                                                   AS 
       hores_reunions, 
       Nz(ts.hores, 0)                                           AS hores_ts, 
       Iif(r.hores - hores_ts > 0, r.hores - Nz(ts.hores, 0), 0) AS r_no_fetes, 
       treballadors.nom_treb, 
       projectes.acronim 
FROM   ((reunions AS r 
         LEFT JOIN (SELECT tl.*, 
                           j.cod_proj 
                    FROM   (timesheet_lines AS tl 
                            LEFT JOIN timesheets AS t 
                                   ON tl.timesheet_id = t.id) 
                           LEFT JOIN justificacions AS j 
                                  ON j.id = t.id_justificacio) AS ts 
                ON ( r.cod_proj = ts.cod_proj ) 
                   AND ( r.cod_treb = ts.cod_treb ) 
                   AND ( r.data = ts.data )) 
        INNER JOIN projectes 
                ON r.cod_proj = projectes.cod_proj) 
       INNER JOIN treballadors 
               ON r.cod_treb = treballadors.cod_treb;

Left join error - JOIN operation

4 Answers4