1

This are my tables:

enter image description here

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:

enter image description here

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:

enter image description here

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
Helyx
  • 329
  • 1
  • 5
  • 17
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) – philipxy May 22 '22 at 20:16

1 Answers1

1

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:

paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • hmm a (simkarten) doesnt have the column modulnummer, so it does not work – Helyx May 22 '22 at 16:54
  • As you discovered: 1) "inner join" assumes that every simkarten has a corresponding module, and every module has a corresponding simkarten1 record. 2) "inner join" should generally be your *first choice". 3) Use "left outer join" if the left table (e.g. "module" in "simkarten join on module") might NOT have any corresponding matches) 4) The alternate syntax I gave you is applicable to any kind of join. – paulsm4 May 22 '22 at 18:57
  • Still cant get it to work. It says "JOIN expression not supported" – Helyx May 22 '22 at 19:18
  • Q: What exactly is the syntax that's giving the "not supported" error? Look here: https://support.microsoft.com/en-us/office/left-join-right-join-operations-ebb18b36-7976-4c6e-9ea1-c701e9f7f5fb. Are you using this ("left join") syntax? – paulsm4 May 22 '22 at 23:43