How to combine these two queries into a single one?

I have two queries that effectively use the same data. I am trying to combine them into one, so I can save space but I am having problems. I am trying to SELECT from the second query. I will demonstrate in my attempt.

Query A:

SELECT TOP 5 ([Parent]), [Game ID]
FROM Games
WHERE [Parent] = 'Zelda'
ORDER BY [Game ID] DESC;


Query B:

SELECT QueryA.[Parent], QueryA.[Game ID]
FROM QueryA
WHERE Mid(QueryA.[Game ID], 5) NOT IN (
SELECT MAX(Mid(Game.[Game ID], 5))
FROM Game
);


Separately, these queries work, but it seems like this can be combined into a single query. I am trying to figure out how. I thought I could just do something like

SELECT QueryA.[Parent], QueryA.[Game ID]
FROM (
SELECT TOP 5 ([Parent]), [Game ID]
FROM Games
WHERE [Parent] = 'Zelda'
ORDER BY [Game ID] DESC
)
WHERE Mid(QueryA.[Game ID], 5) NOT IN (
SELECT MAX(Mid(Game.[Game ID], 5))
FROM Game
);


But that's not the case. Seems basic, but I am trying to figure it out.

WHERE

1 Answer
1

Your problem can be that you forget the alias name for your subquery. Try this:

SELECT QueryA.[Parent],
QueryA.[Game ID]
FROM
(SELECT TOP 5 ([Parent]), [Game ID]
FROM Games
WHERE [Parent] = 'Zelda'
ORDER BY [Game ID] DESC) as QueryA
WHERE Mid(QueryA.[Game ID], 5) NOT IN
(SELECT MAX(Mid(Game.[Game ID], 5))
FROM Game);


And if this is not the solution, is happening some error that you could show?

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.