All geek questions in one place

Sql left join + one to many relationships

I have three tables - node, content_type_product and share_content. There can be a 1: N ratio between the node and share_content relationships. I want to pull only one record per identifier. If there are multiple entries in share_content, I want the latter, i.e. the highest sc.auto_id value

SELECT sc.uid, n.uid, n.nid, sc.message FROM node n LEFT JOIN content_type_product p ON n.nid = p.nid LEFT JOIN share_content sc ON n.nid = sc.nid WHERE n.nid = 40513 GROUP BY sc.nid ORDER BY sc.auto_id
+6
source share
2 answers

Why are you joining content_type_product ?? But aside, try

  SELECT c.uid, n.uid, n.nid, c.message FROM node n LEFT JOIN share_content c ON c.nid = n.nid And c.auto_id = (Select Max(auto_id) From share_content Where nid = p.nid ) Where n.nid = 40513 ORDER BY c.auto_id
+5
source

Try:

 SELECT sc.uid, n.uid, n.nid, sc.message FROM node n left join content_type_product p on n.nid = p.nid LEFT JOIN share_content sc on n.nid = sc.nid WHERE n.nid = 40513 GROUP BY sc.nid, sc.auto_id ORDER BY sc.auto_id DESC
+1
source

Source: https://habr.com/ru/post/905159/

More articles:


All Articles