0

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?

  • looking at it a bit closer the term: like an inner join, might be incorrect – Toskan Nov 26 '14 at 8:34
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
        ) ;
  • do you understand what my error in thinking was? – Toskan Nov 26 '14 at 8:56
  • Your first query is not wrong wrong. It's on the right direction and it will get all the info you want but not in 4 rows. – ypercubeᵀᴹ Nov 26 '14 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.