Mysql – Simple left outer join leaving me with inner join

join;MySQL

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?

Best Answer

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
        ) ;

Related Solutions

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

No child at all, so CHILD.id is NULL.
Child matches.
Child does not match and no other matching child.
Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

Filter BASE on id and date range. Still includes rows with non-matching children.
Assign aliases for identical column names
Tag rows where child matches the date

In the final SELECT:

Return all rows with matching children
Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.

Sql-server – Outer self join filters vs subqueries

Your first query works as an inner join because the y.TransType = 'used' condition which uses the right table is in the where clause.

Your second query can be rewritten without derived tables by simply moving that condition to the on clause:

select x.*, y.*
from factI as x
left join factI as y on  x.tickedId = y.tickedId
                     and y.TransType = 'used'
where x.TransType = 'sold' ;

Best Answer

Related Solutions

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

Sql-server – Outer self join filters vs subqueries

Related Question