mysql - SQL query with joins and optional matching -

i programming mapper info out of database.

here database:

• spel( id, naam)

• spelbord(id, spel_id, level)

  fk spel_id --> id in spel

• veld(spelbord_id, posx, posy, veldnr, istarget, toegang)

  fk spelbord_id --> id in spelbord

• entiteit(spelbord_id, posx, posy, entiteitnr)

  fk spelbord_id --> spelbord_id in veld

  fk posx --> posx in veld

  fk posy --> posy in veld

so query give me: posx, posy, veldnr, toegang, istarget (and entiteitnr if exists) of each veld.

for example:

1, 2, 1, true, false, 1 

(there entiteit on veld), or

1, 3, 1, true, false, null 

(there no entiteit on veld).

this query doesn't seem work

select v.posx, v.posy, v.veldnr, v.toegang, v.istarget, i.entiteitnr  spel s join spelbord sb on sb.spel_id = s.id  join veld v on sb.id = v.spelbord_id  join entiteit on v.posx = i.veld_posx , v.posy = i.veld_posy  order posx,posy 

with query i'm getting output there indeed entiteit connect it.

what need change rows there no entiteit match?

your query close; need add 1 word, left, join between enteitit , other tables. looks though don't need use spel or spelbord @ data need select:

select v.posx, v.posy, v.veldnr, v.toegang, v.istarget, i.entiteitnr   veld v   left join entiteit on v.posx = i.veld_posx , v.posy = i.veld_posy  order posx, posy 

the presence/absence of spel , spelbord should not affect result. if make difference, can add them back, unlikely problem unless id columns in spel , spelbord not primary keys. (you documented foreign keys; that's good. document candidate keys too.)