MS Access join with 3 tables, but showing all values of the main table-CodePudding

This are my tables:

Each zaehler can have 0 or 1 module and each module can have 0 or 1 simkarten zaehler is my "main table"

i want a query that shows all zaehler and if there is a module, show all fields of module and if the module has a simkarten also show all entrys of that table.

I tried this:

SELECT *
FROM (zaehler
left JOIN module ON zaehler.modulnummer = module.modulnummer )

and the result was this:

Looks good, but i also want to see all columns of simkarten, so i tried this:

SELECT *
FROM (simkarten
INNER JOIN module ON simkarten.simnr = module.simnr )
INNER JOIN zaehler ON module.modulnummer = zaehler.modulnummer

The result is this:

I get only one entry back. It is the entry that has both, a module and a simkarten entry.

What i want is to see all zaehler entries, like in my first query, but also all fields of simkarten like in my 2nd query.

How can I archive this?

UPDATE:

Its working now, this is the query i used:

SELECT *
FROM (zaehler
LEFT JOIN module ON zaehler.modulnummer = module.modulnummer)
LEFT JOIN simkarten ON module.simnr = simkarten.simnr

CodePudding user response：

Try this (untested):

SELECT a.zaehlernummer,a.herrsteller,a.modulnummer,c.simmnr,c.ip
FROM simkarten a
INNER JOIN module b ON a.modulnummer = b.modulnummer
INNER JOIN simkarten1 c ON b.simnr =  c.simnr

Look here for additional details: