sql列之间的一对多和一对一的关系

I have no idea if the title matches what I am trying to achieve in the query given below. The example might not be great but it serves what I am trying to achieve. There are three results that I am trying to get as shown below. Can this be done with a single query showing all the results in a better way?I have no idea if the title matches what I am t