Joining 2 tables with different row amount

I have a problem with joining 2 tables with different quantity of rows. I tried LEFT JOIN, but it does not work, and I suppose that’s because of the ID_advertentie.

Query:

SELECT * FROM table1
LEFT JOIN table2 ON table1.ID_kenmerk = table2.ID_kenmerk
WHERE table1.categorie = 1 AND table2.ID_advertentie = 17

How can I write one query with the outcome mentioned as below the table examples?

Table 1
ID_kenmerk      Categorie     Naam_kenmerk    Plaatje_kenmerk    Data_type
1           -       1      -   Kenmerk a   -   plaatje1.gif   -    INT
2           -       1      -   Kenmerk b   -   plaatje2.gif   -    BOOL
3           -       1      -   Kenmerk c   -   Plaatje3.jpg   -    INT
4           -       1      -   Kenmerk d   -   plaatje4.jpg   -    VARCHAR
5           -       2      -   Kenmerk F   -   plaatje1.gif   -    INT
6           -       2      -开发者_StackOverflow   Kenmerk G   -   plaartje2.gif  -    VARCHAR

Table 2
ID_kenmerk_data     ID_kenmerk     ID_advertentie      value
1                -      1       -       17         -   Test1
2                -      2       -       17         -   Test2
3                -      3       -       17         -   Test3
4                -      1       -       23         -   lala1
5                -      2       -       23         -   lala2
6                -      3       -       23         -   ajdk2

Outcome query (WHERE ID_advertentie = 17!)

ID_kenmerk    Naam_kenmerk     value
1          -   Kenmerk a   - Test1
2          -   Kenmerk b   - Test2
3          -   Kenmerk c   - Test3
4          -   Kenmerk d   - NULL

---

SELECT *
FROM table1
LEFT JOIN table2 ON table1.ID_kenmerk = table2.ID_kenmerk AND table2.ID_advertentie = 17
WHERE table1.categorie = 1 

Just about anything that you use in the WHERE clause can also be used inside the ON clause. But moving the table2.ID_advertentie = 17 from the WHERE to the ON clause will add a NULLed row on the right hand side if there is no match instead of eliminating it.

(Edit) Alternate method:

SELECT *
FROM table1
LEFT JOIN table2 ON table1.ID_kenmerk = table2.ID_kenmerk
WHERE table1.categorie = 1 AND (table2.ID_kenmerk_data IS NULL OR table2.ID_advertentie = 17)

---

You were right that you need a LEFT JOIN. But when you do a (LEFT) OUTER JOIN, any WHERE condition on the right table cancels the outer join.

Solution is to move the condition regarding (the right) table2 (the table2.ID_advertentie = 17 one) from the WHEREto the ON clause:

SELECT * 
FROM table1
LEFT JOIN table2 
  ON  table1.ID_kenmerk = table2.ID_kenmerk
  AND table2.ID_advertentie = 17             --- this condition moved
WHERE table1.categorie = 1