SQL query: LEFT JOIN in MS access

Question

I have the following problem in this SQL query:

SELECT fa.Filiale_ID, fa.Datum_Beauftragung, fa.Auftragstyp, fad.Tiefbau, fh.HW_ID, fh.Anzahl
FROM Filiale_Aufträge AS fa LEFT JOIN Filiale_Auftragsdaten AS fad ON fa.Filiale_ID = fad.Filiale_ID
FROM Filiale_Aufträge AS fa LEFT JOIN Filiale_Hardware AS fh ON fa-Filiale_ID = fh.Filiale_ID
WHERE (((fa.Filiale_ID) Not In (SELECT Filiale_ID
  FROM Filiale_Aufträge
  WHERE Auftragstyp IN ('Aufbau', 'Service', 'Aufbau vorl', 'Fertigfundamente', 'Integration Bestand',  'Parkplatzmakierungen', 'RFID-Karten', 'Roaming', 'Hardware') 

)) AND ((fa.Datum_Beauftragung) Is Not Null) AND ((fa.Auftragstyp)='Begehung') AND ((fad.Tiefbau)='Ja' Or (fad.Tiefbau)='Nein'));

i also tried, and got the same problem:

SELECT fa.Filiale_ID, fa.Datum_Beauftragung, fa.Auftragstyp, fad.Tiefbau, fh.HW_ID, fh.Anzahl
FROM Filiale_Aufträge AS fa 
LEFT JOIN 
    (SELECT Filiale_ID, Tiefbau 
     FROM Filiale_Auftragsdaten) 
     AS fad ON fa.Filiale_ID = fad.Filiale_ID
LEFT JOIN 
    (SELECT Filiale_ID, HW_ID, Anzahl 
     FROM Filiale_Hardware) 
     AS fh ON fa.Filiale_ID = fh.Filiale_ID
WHERE 
    fa.Filiale_ID NOT IN (
        SELECT Filiale_ID
        FROM Filiale_Aufträge
        WHERE Auftragstyp IN ('Aufbau', 'Service', 'Aufbau vorl', 'Fertigfundamente', 'Integration Bestand', 'Parkplatzmakierungen', 'RFID-Karten', 'Roaming', 'Hardware')
    )
    AND fa.Datum_Beauftragung IS NOT NULL 
    AND fa.Auftragstyp = 'Begehung' 
    AND (fad.Tiefbau = 'Ja' OR fad.Tiefbau = 'Nein');

I get the following error message My error message: Syntax error (missing operator) in query expression"fa.Filiale_ID = fad.Filiale_ID FROM Filiale_Aufträge AS fa LEFT JOIN Filiale_Hardware AS fh ON fa.Filiale_ID = fh.Filiale_I"

I think it has something to do with the FROM LEFT JOIN, because as soon as I run each command individually, the query works, maybe someone knows, ChatGPT couldn't help me :D

Greetings Johannes

If you are using MS-Access you can remove the tag. Inconsistent tagging doesn't improve the attention you get. — jarlh, May 09 '23 at 08:26
Can you also show us the error message you get as plain text. — jarlh, May 09 '23 at 08:27
BTW, doesn't Access require parentheses for each additional join? — jarlh, May 09 '23 at 08:28
My error message: Syntax error (missing operator) in query expression"fa.Filiale_ID = fad.Filiale_ID FROM Filiale_Aufträge AS fa LEFT JOIN Filiale_Hardware AS fh ON fa.Filiale_ID = fh.Filiale_I" — Johannes, May 09 '23 at 08:35
I've already written each LEFT JOIN in individual brackets and got the same problem: — Johannes, May 09 '23 at 08:37
Is there a _good_ reason why you specifically chose MS Access for this project? I'm asking because MS Access' SQL engine and capabilities is essentially entirely unchanged since Access 2003 - which means chosing MS Access for a project means you're chosing to not use any of the many compelling, if not outright essential features now common to the whole smorgasbord of competing RDBMS (including MS SQL Server) which at least try to keep-up with the ISO SQL spec - instead of being stuck with SQL-92 and archaic parenthesised JOINs. Ew. _Friends don't let Friends use MS Access_. — Dai, May 09 '23 at 08:40
@Johannes Your updated SQL with those extra parentheses is still incorrect: **every** `JOIN` step means introducing another layer of nested parens - the end-result of a 3 or 4-way `JOIN` means you end up with SQL that looks more like Lisp. — Dai, May 09 '23 at 08:41
See here: [SQL multiple join statement](https://stackoverflow.com/questions/7854969/sql-multiple-join-statement) — Dai, May 09 '23 at 08:42
@Johannes That is truly horrifying - and I really do mean that. That's like a company chosing to use Cobol for a greenfield system in the early-1990s - or a country chosing to build new coal power plants in 2023: it boggles the mind. The _only_ possible justification I can think-of is the requirement for a 4GL-like system (i.e. forms, reports, tables and queries all in a single package) - which isn't unreasonable in-itself, except that because Access has stagnated to such a _horrible_ degree it means that you might want to consider quitting your job to preserve your sanity. — Dai, May 09 '23 at 08:51
@Dai thanks for the link, this help a lot and i solved my problem thank you! — Johannes, May 09 '23 at 09:09

score 0 · Answer 1 · answered May 09 '23 at 09:12

As a preface, I stress that I am sharply critical only of using Access as a SQL database in 2023: I see nothing wrong (and in fact, many advantages) to using Access as a RAD front-end development tool for MS SQL Server.

In our company it was decided to work with ms access

In all sincerity I suggest it's time you find a new company to work for;

_{while Access does still have genuine utility as a familiar 4GL-like tool for a whole generation of individuals and small-business operators, and as a RAD frontend for SQL Server, I simply cannot believe that any team of competent, informed computing professionals today would actively chose to adopt MS Access for a SQL database: it's the software equivalent of anyone chosing to building a coal power plant: not only a significant anachronism but it also imposes significant and unnecessary restrictions on the business-utility of whatever database is being built because you can't use any of the significant advances in ISO SQL that happened since Access' SQL support was frozen-in-time with only its partial support for the bare essentials of SQL-92.}

Anyway, here's what you want: it works for me in an Access DB I made using using your table and column names.

The thing to notice is how each parentheses pair envelopes all previous JOIN clauses:



SELECT
    fa.Filiale_ID,
    fa.Datum_Beauftragung,
    fa.Auftragstyp,
    fad.Tiefbau,
    fh.HW_ID,
    fh.Anzahl

FROM
    (
        (
            Filiale_Aufträge AS fa 
            LEFT JOIN
            (
                SELECT
                    Filiale_ID,
                    Tiefbau 
                FROM
                    Filiale_Auftragsdaten
            ) AS fad ON fa.Filiale_ID = fad.Filiale_ID
        )
    
        LEFT JOIN 
        (
            SELECT
                Filiale_ID,
                HW_ID,
                Anzahl 
            FROM
                Filiale_Hardware
        ) AS fh ON fa.Filiale_ID = fh.Filiale_ID
    )

WHERE 
    fa.Filiale_ID NOT IN
    (
        SELECT
            Filiale_ID
        FROM
            Filiale_Aufträge
        WHERE
            Auftragstyp IN ( 'Aufbau', 'Service', 'Aufbau vorl', 'Fertigfundamente', 'Integration Bestand', 'Parkplatzmakierungen', 'RFID-Karten', 'Roaming', 'Hardware' )
    )
    AND
    fa.Datum_Beauftragung IS NOT NULL 
    AND
    fa.Auftragstyp = 'Begehung' 
    AND
    (
        fad.Tiefbau = 'Ja'
        OR
        fad.Tiefbau = 'Nein'
    );

score 0 · Answer 2 · answered May 09 '23 at 09:12

I was able to solve the problem myself by putting each JOIN statement in an extra bracket

SELECT fa.Filiale_ID, fa.Datum_Beauftragung, fa.Auftragstyp, fad.Tiefbau, fh.HW_ID, fh.Anzahl
FROM (Filiale_Aufträge AS fa 
LEFT JOIN Filiale_Auftragsdaten AS fad ON fa.Filiale_ID = fad.Filiale_ID)
LEFT JOIN Filiale_Hardware AS fh ON fa.Filiale_ID = fh.Filiale_ID
WHERE fa.Filiale_ID NOT IN (
        SELECT Filiale_ID
        FROM Filiale_Aufträge
        WHERE Auftragstyp IN ('Aufbau', 'Service', 'Aufbau vorl', 'Fertigfundamente', 'Integration Bestand', 'Parkplatzmakierungen', 'RFID-Karten', 'Roaming', 'Hardware')
    )
    AND fa.Datum_Beauftragung IS NOT NULL 
    AND fa.Auftragstyp = 'Begehung' 
    AND (fad.Tiefbau = 'Ja' OR fad.Tiefbau = 'Nein');

You might want to consider adopting a consistent SQL formatting and indentation style - otherwise nontrivial queries like yours become almost impossible to quickly read. (I have my own personal style (as seen in my answer) but I appreciate it isn't the most economical use of vertical screen-space, so if you're stuck on a `1366x768px` display you might consider a more compact style - but the important thing is _to be consistent_. — Dai, May 09 '23 at 09:14

SQL query: LEFT JOIN in MS access

2 Answers2