CODEWITHSUNDEEP
mysqlsql
Faryal Khan
Faryal Khan

Reputation: 869

How to write a select statement inside another select in SQL

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;

Upvotes: 4

Views: 47475

Answers (3)

Taryn
Taryn

Reputation: 247870

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;

Upvotes: 10

Hamza Waqas
Hamza Waqas

Reputation: 629

Alias it as a virtual table. Change something like

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) as virtual_column_alias;

Check this out. Hope this will work.

Upvotes: 0

Otávio Décio
Otávio Décio

Reputation: 74310

Adding to bluefeet's answer, you may want to check for reserved words. "File" for example is a reserved word in sql server.

Upvotes: 0

Related Questions