How to write a select statement inside another select in SQL

Question

Can anyone tell me what is wrong with this query? it gives a syntax error near the 2nd select

SELECT b.mc_boxes_idmc_boxes,
          t.idtitles, 
          t.title,
          t.languages_idlanguages,
          MAX(h.idtitle_history),
          MAX(h.edition)
          (SELECT h.preview, h.file WHERE h.idtitle_history = MAX(h.idtitle_history))
                        FROM mc_boxes_has_titles b
                        LEFT JOIN titles t ON b.titles_idtitles = t.idtitles
                        LEFT JOIN title_history h ON h.titles_idtitles = t.idtitles
                        WHERE b.mc_boxes_idmc_boxes = 12
                        AND h.edition IS NOT NULL
                        GROUP BY b.mc_boxes_idmc_boxes, idtitles
                        ORDER BY b.sortorder;

Answer 1

looks like you are missing a comma after MAX(h.edition)

SELECT b.mc_boxes_idmc_boxes,
          t.idtitles, 
          t.title,
          t.languages_idlanguages,
          MAX(h.idtitle_history),
          MAX(h.edition), 
          (SELECT h.preview, h.file WHERE h.idtitle_history = MAX(h.idtitle_history))
FROM mc_boxes_has_titles b
LEFT JOIN titles t ON b.titles_idtitles = t.idtitles
LEFT JOIN title_history h ON h.titles_idtitles = t.idtitles
WHERE b.mc_boxes_idmc_boxes = 12
     AND h.edition IS NOT NULL
GROUP BY b.mc_boxes_idmc_boxes, idtitles
ORDER BY b.sortorder;

besides the comma, you are selecting two fields in your subquery

SELECT b.mc_boxes_idmc_boxes,
          t.idtitles, 
          t.title,
          t.languages_idlanguages,
          MAX(h.idtitle_history),
          MAX(h.edition), 
          (SELECT preview FROM title_history WHERE idtitle_history = MAX(h.idtitle_history)),
          (SELECT [file] FROM title_history WHERE idtitle_history = MAX(h.idtitle_history))
FROM mc_boxes_has_titles b
LEFT JOIN titles t ON b.titles_idtitles = t.idtitles
LEFT JOIN title_history h ON h.titles_idtitles = t.idtitles
WHERE b.mc_boxes_idmc_boxes = 12
     AND h.edition IS NOT NULL
GROUP BY b.mc_boxes_idmc_boxes, idtitles
ORDER BY b.sortorder;