sql left join +one to many relationship

Question

I have three tables - node, content_type_product and share_content. There may be a 1:N relationship beetween node and share_content. I want to pull out only one record per id. If there is multiple records in share_content, I want the latest one, that is the highest value of sc.auto_id

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

The [example](http://sscce.org/) is incomplete without schema (as `CREATE TABLE` statements), sample data (as `INSERT` statements) and desired results for the sample data. — outis, Jan 05 '12 at 02:37
possible duplicate of [Select Rows with Maximum Column Value group by Another Column](http://stackoverflow.com/questions/1425240/), [How To Find The Top String Column Value in a Grouped Query](http://stackoverflow.com/questions/1443979/), [SQL Group by & Max](http://stackoverflow.com/questions/1299556/) — outis, Jan 05 '12 at 02:40
... [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/), [Fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/), and many, many others. — outis, Jan 05 '12 at 02:51
"may be a 1:N relationship" -- a.k.a. 1:0..M correspondence ("one to zero or more"). — onedaywhen, Jan 05 '12 at 09:11

Charles Bretana · Accepted Answer · 2012-01-05T03:40:37.087

6

Why are you joining to content_type_product ?? But that 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

edited Jan 05 '12 at 03:40

answered Jan 05 '12 at 02:51

Charles Bretana

143,358
22
150
216

Edited it because your subquery would not return the needed columns to make the join, it would only return the MAX(auto_id) value. – Francisco Soto Jan 05 '12 at 02:54
hmm, how can c.auto_id=uid, message?? – sonofthom Jan 05 '12 at 03:11
reedited to return it to what was submitted - because that's the only column the subquery NEEDs to return, added code for message field – Charles Bretana Jan 05 '12 at 03:41

score 1 · Answer 2 · answered Jan 05 '12 at 02:34

1

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

answered Jan 05 '12 at 02:34

Petar Sabev

848
6
12

Thank you - works, however - we are looking to group only by sc.nid - as we want only one result. – sonofthom Jan 05 '12 at 02:39

sql left join +one to many relationship

2 Answers2