I'm currently struggling with a (fairly easy I think) SQL problem but I cant seem to figure it out.
Suppose I have the following tables:
Persons
+-------+----+
| name | id |
+-------+----+
| Steve | 1 |
| John | 2 |
+-------+----+
Information
+----------+----+-----------+---------------------------------------------------+
| type | id | linked_id | info |
+----------+----+-----------+---------------------------------------------------+
| persons | 1 | 1 | Info about Steve |
| cars | 2 | 1 | Info about a car, aka not stored in Persons table |
+----------+----+-----------+---------------------------------------------------+
If I would want the Persons table and a subset of Information (type=persons), my query would be something like:
SELECT *
FROM Persons
LEFT JOIN Information ON Persons.id = Information.linked_id
WHERE (Information.type = "persons" OR Information.type IS NULL)
And this should be what I expect:
Desired Result
+-------+----+----------+------+------------+------------------+
| name | id | type | id | linked_id | info |
+-------+----+----------+------+------------+------------------+
| Steve | 1 | persons | 1 | 1 | Info about Steve |
| John | 2 | NULL | NULL | NULL | NULL |
+-------+----+----------+------+------------+------------------+
But this is the actual result:
+-------+----+----------+----+-----------+------------------+
| name | id | type | id | linked_id | info |
+-------+----+----------+----+-----------+------------------+
| Steve | 1 | persons | 1 | 1 | Info about Steve |
+-------+----+----------+----+-----------+------------------+
The "John" Person row, who doesn't yet have an Information row, should also be included in the result but it isn't.
What am I doing wrong? Shouldn't the OR Information.type IS NULL part of my query take care of this? The row isn't included though. Am I missing something else?
