CODEWITHSUNDEEP
mysqlsqlsubquery
Geoff Canyon
Geoff Canyon

Reputation: 83

A better way to subquery

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

Upvotes: 6

Views: 309

Answers (3)

miyasudokoro
miyasudokoro

Reputation: 1745

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

Upvotes: 1

IuR
IuR

Reputation: 176

First Query Will Get the parents who have no children While Second Query will get the parent whose most recent child article is not of a certain set of types. UNION will itself provide you the DISTINCT result set.

There are so many nested select but don't worry all of them are applying filters on your already loaded main result set so it will not effect performance, you can test it while executing query on Db console.

SELECT
 CONCAT('http://www.sitename.com/', n.id, '.html') AS URL,
 n.added,
 u.email,
 n.body
 FROM
 warehouse.article n
 JOIN site.user u ON n.user_id = u.id
 JOIN warehouse.article_param np ON np.id = n.id AND np.param_name = 'ready' AND <br/> np.param_value = 'true'
 LEFT JOIN warehouse.article_rel r ON r.parent_nid = n.id
 WHERE
 n.type_id = 1234  AND r.id IS NULL

 UNION

 SELECT URL,added,email,body FROM
 (SELECT * FROM
(SELECT

 CONCAT('http://www.sitename.com/', n.id, '.html') AS URL,
 n.added,
 u.email,
 n.body,
 nr.type_id
 FROM
 warehouse.article n
 JOIN site.user u ON n.user_id = u.id
 JOIN warehouse.article_param np ON np.id = n.id AND np.param_name = 'ready' AND <br/> np.param_value = 'true'
 JOIN warehouse.article_rel r ON r.parent_nid = n.id
 JOIN warehouse.article nr ON r.child_nid=nr.id
 WHERE
 n.type_id = 1234
 ORDER BY n.id DESC
 ) AS tbl1
GROUP BY id
Where  type_id NOT IN (2245,5443)

Upvotes: 0

fankt
fankt

Reputation: 1047

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, c.type_id 
           from warehouse.article c
           left join warehouse.article_rel r on r.child_nid = c.id and r.parent_nid = n.id     
           order by c.added desc 
           limit 1
      ) child_type
      on child_parent_nid = n.id
where
    n.type_id = 1234 and (child_type.type_id not in (2245,5443) or child_type.type_id is null)
order by 
    n.updated desc

Only test in my mind, not sure it's 100% correct or not. Any correction is very welcome. :)

Upvotes: 0

Related Questions