如何用子查询获取Ryan租用的2004年款游戏机对应游戏列表？

Let's start by breaking down what's going wrong with your current query:

• Missing Join Condition: You're referencing both GameRental and Console in the main query but haven't linked them with a join clause (like ON GameRental.ConsoleID = Console.ID). This creates a Cartesian product—every game Ryan rented gets paired with every console in the table, which is why you're seeing all console years instead of just the ones tied to his games.
• Unlinked EXISTS Subquery: Your EXISTS check only verifies that any 2004 console exists in the database, not that the specific game Ryan rented runs on one. It doesn't connect back to the main query's game/console pairing, so it doesn't filter results correctly.
• Unnecessary Fields in EXISTS: EXISTS only cares if the subquery returns at least one record—you don't need to select GameTitle or YearDev here; using SELECT 1 is more efficient and clear.

Correct Query with EXISTS Subquery #

Assuming your GameRental table has a field (like ConsoleID) that links to the Console table's primary key (e.g., ID), here's the fixed version:

SELECT GameTitle, YearDev
FROM GameRental
JOIN Console ON GameRental.ConsoleID = Console.ID
WHERE GameRental.RentedBy = 'Ryan'
AND EXISTS (
    SELECT 1
    FROM Console c
    WHERE c.ID = GameRental.ConsoleID
    AND c.YearDev = '2004'
);

Alternative: Using IN Subquery #

If you prefer an IN clause instead, this works too:

SELECT GameTitle, YearDev
FROM GameRental
JOIN Console ON GameRental.ConsoleID = Console.ID
WHERE GameRental.RentedBy = 'Ryan'
AND GameRental.ConsoleID IN (
    SELECT ID
    FROM Console
    WHERE YearDev = '2004'
);

Note on Table Relationships #

If your tables use a different linking field (like ConsoleName instead of ID), just swap out the matching columns in the ON clause and subquery. The key is ensuring you're only pairing each rented game with its actual console, then filtering to only those consoles released in 2004.

内容的提问来源于stack exchange，提问作者user2824852