MySQL left join filter -

i have 2 related tables shown below need join new , updated records. these representative examples simplified table structure.

the first table table of items e.g. 'notes' contains existing notes 'new' notes , updates existing notes.

the second table contains details of pending changes/updates note. example, if note has been modified, row cloned modifications , row added pendingupdates table id of original note, id of row updates , user made update. if new note added workflow similar expect original id , modified id refer same row.

notes: +----+---------------------+----------------------------+ | id | title               | text                       |  +----+---------------------+----------------------------+ |  1 | test note | blah blah blah             |  |  2 | note 2              | sdfsadfasdf                |  |  3 | note 3              | jklhjklhjklhjk             | |  4 | note 3              | update note3 | |  5 | note 3              | update             |  |  6 | note 4              | new note                   |  +----+---------------------+----------------------------+  pendingupdates: +----+------------+---------+------+ | id | originalid | cloneid | user | +----+------------+---------+------+ |  1 |          3 |       4 |    1 | |  2 |          3 |       5 |    1 | |  3 |          6 |       6 |    2 | +----+------------+---------+------+ 

what run select notes , include if note has changes (with list of updates) , new notes.

so above example return row 1,2,3, , 6. row 3 have 4,5 listed updates.

example of desired results:

+----+------------+-------------+------+--------+---------------------+ | id | haschanges | isnewrecord | p_id | clones | title               | +----+------------+-------------+------+--------+---------------------+ |  1 | no         | no          | null |  null  | test note | |  2 | no         | no          | null |  null  | note 2              | |  3 | yes        | no          |    1 |  4,5   | note 3              | |  6 | yes        | yes         |    3 |  6     | note 4              | +----+------------+-------------+------+--------+---------------------+ 

i've tried many different combinations of queries , have yet need. here's i've got pretty close, including rows 4 & 5 in result set don't want.

select      o.id,     if(o.id = p.originalid, 'yes', 'no') haschanges,     if(p.cloneid = p.originalid,         'yes',         'no') isnewrecord,     p.id p_id,     group_concat(p.cloneid         separator ',') clones,     o.title     `notes` o         left join     `pendingupdates` p on (o.id = p.originalid) group id; 

this returns:

   +----+------------+-------------+------+--------+---------------------+    | id | haschanges | isnewrecord | p_id | clones | title               |    +----+------------+-------------+------+--------+---------------------+    |  1 | no         | no          | null |  null  | test note |    |  2 | no         | no          | null |  null  | note 2              |    |  3 | yes        | no          |    1 |  4,5   | note 3              |  ->|  4 | no         | no          | null |  null  | note 3              |  ->|  5 | no         | no          | null |  null  | note 3              |    |  6 | yes        | yes         |    3 |  6     | note 4              |    +----+------------+-------------+------+--------+---------------------+ 

---

solution:

here final solution ended using, similar fthiella's.

thanks again helped out this.

select      m.id,     if(m.max_pending not null, 'yes', 'no') haschanges,     if(m.id = pendingupdates.cloneid, 'yes', 'no') isnew,     m.clones,     m.pendingids,     m.title (select             notes.id,            max(pendingupdates.id) max_pending,           group_concat(pendingupdates.id) pendingids,           group_concat(pendingupdates.cloneid) clones,           max(title) title        notes       left join pendingupdates on notes.id = pendingupdates.originalid       group notes.id       ) m           left join pendingupdates on m.max_pending = pendingupdates.id     m.id not in (select                       cloneid                  pendingupdates                  cloneid not in (select                                            originalid                                          pendingupdates)                 ) order id; 

http://sqlfiddle.com/#!2/8566e/1/0

i have rewritten query way (your original query makes use of non-aggregated columns, , value of undetermined in circumstances):

select   m.id,   case when m.max_pending not null        'yes' else 'no' end haschanges,   case when m.id=pendingupdates.cloneid        'yes' else 'no' end isnew,   m.min_pending pid,   m.clones,   m.title (   select     notes.id,     max(pendingupdates.id) max_pending,     min(pendingupdates.id) min_pending,     group_concat(pendingupdates.cloneid) clones,     max(title) title       notes left join pendingupdates     on notes.id = pendingupdates.originalid   group     notes.id) m left join pendingupdates   on m.max_pending = pendingupdates.id   m.id not in (select cloneid pendingupdates                cloneid not in (select originalid pendingupdates)) 

please see fiddle here.