Simple left outer join leaving me with inner join

Question

So I have a table with each record being a change to an object.

e.g. in the app

you open your project with a project id in this project you edit an object name hit save

that generates new record with datetime stamp and object name change value, related to the project id that you work on

tricky part is, project_id can be null. Still the object_id is unique. So I know from the object id, on which project I am.

table change with object_id and datetime being a composite primary key.

e.g.

object_id | datetime            | objectname  | project_id
55        | 2014-03-24 09:55:40 | somevalue 1 | 10
77        | 2014-02-24 09:55:40 | somevalue   | 88
55        | 2014-10-24 09:55:40 | somevalue2  | null
55        | 2014-11-24 09:55:40 | somevalue3  | 10
22        | 2014-12-24 09:55:40 | somevaluex  | 10

What I want to get is all the records, that belong to project 10

that would be

object_id | datetime            | objectname  | project_id
55        | 2014-03-24 09:55:40 | somevalue 1 | 10
55        | 2014-10-24 09:55:40 | somevalue2  | null
55        | 2014-11-24 09:55:40 | somevalue3  | 10
22        | 2014-12-24 09:55:40 | somevaluex  | 10

What I tried and failed horribly is an inner join on the same table

I tried this:

SELECT  * FROM change
LEFT JOIN (SELECT * from change AS x WHERE x.project IS NULL) AS change2
ON change2.object_id = change.object_id 
WHERE change.project = 10

This gives me only records with null elements for project id, like for an INNER JOIN. I tried as well:

SELECT  * FROM change
LEFT JOIN change AS change2
ON change2.object_id = change.object_id 
AND  change2.project is NULL
WHERE change.project = 10

Funny, it gives the same result (like an inner join would, e.g. only records with null as project)

What am I missing?

Answer 1

I think you need a UNION or an OR with EXISTS:

SELECT c.* 
FROM change AS c
WHERE c.project_id = 10
   OR c.project_id IS NULL
  AND EXISTS
        ( SELECT *
          FROM change AS x 
          WHERE x.project_id = 10
            AND x.object_id = c.object_id
        ) ;