A better way to subquery

Question

I have a DB where the article table has a many-to-many relationship to itself (through article_rel) and articles have types. Parents have a type of 1234, while children can be one of several types.

I am trying to find those parents who either: have no children; or, if they have children the most recent child article is not of a certain set of types.

The following works reasonably well for the second part, but doesn't address the first part. Every attempt I've made to pull out the subquery so I can reference the value it returns (to add "or is null") has failed. I'm also wondering in general if there is a better way to write something like this.

SELECT 
 CONCAT('http://www.sitename.com/', n.id, '.html') as URL,
 n.added,
 u.email,
 n.body
 #c.body
 FROM
     warehouse.article n 
 inner join site.user u on n.user_id = u.id
 inner join warehouse.article_param np on np.id = n.id and np.param_name = 'ready' and np.param_value = 'true'
 where
     n.type_id = 1234 
 and
     (select 
           c.type_id 
      from 
           warehouse.article c, 
           warehouse.article_rel r
      where
           r.child_nid = c.id
           and r.parent_nid = n.id
      order by 
           c.added desc 
      limit 1)
      not in (2245,5443)
 order by 
      n.updated desc

Answer 1

You should be able to use MAX(added) to find the latest-added children only. Derived table x finds the latest-added child's date for the parent n.id (see this if that part doesn't make sense to you). Then t finds the data about that latest-added child. I used the left join to get the latest-added child of n.id because if there isn't a child, then it will leave a null in the child's place, giving you all articles that have no children.

SELECT n.added, CONCAT('http://www.sitename.com/', n.id, '.html') as URL,
u.email, n.body #c.body
FROM warehouse.article n 
inner join site.user u on n.user_id = u.id
inner join warehouse.article_param np on np.id = n.id and np.param_name = 'ready' and np.param_value = 'true'
left join (SELECT r.parent_nid, MAX(added) as latest
              FROM warehouse.article c
              INNER JOIN warehouse.article_rel r on c.id = r.child_nid
              GROUP BY r.parent_nid) as x on x.parent_nid = n.id
left join warehouse.article t on t.added = x.latest
where n.type_id = 1234 and (t.type_id is null or t.type_id not in (2245,5443))
order by n.updated desc

If there is a chance that there is more than one article with the exact same added date, then you have to use a derived table for t to check for parentage:

left join (SELECT c.type_id, c.id, c.added, r.parent_nid
           FROM warehouse.article c
           INNER JOIN warehouse.article_rel r on c.id = r.child_nid)
           as t on t.parent_nid = n.id and t.added = x.latest