The best way to subquery

Question

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

I try to find those parents who either do not have children; or, if they have children, the most recent children's article does not have a specific set of types.

The following works are well suited for the second part, but do not affect the first part. Every attempt I made to pull out a subquery so that I could reference the return value (add "or is null") failed. I am also wondering 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 can use MAX (added) to find only new users. The date x of the last addition of the parent n.id was found in the view x (see this if this part does not make sense to you). Then t finds data about this last added child. I used the left join to get the last added child of n.id, because if there is no child, it will leave zero in the child place, giving you all the 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 possibility that there is more than one article with the same date added, then you need to use the view for t to check the origin:

 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 

Answer 2

 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 

Test only in my opinion, not sure if it is 100% correct or not. Any correction is very welcome. :)

Answer 3

The first request will be received by parents who have no children. While Second Query will receive a parent whose last child’s article does not have a specific set of types. UNION will provide you with the DISTINCT result set.

There are so many nested selects, but don’t worry that they all apply filters to the already loaded main result set, so it will not affect performance, you can test it when executing a query on the 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)