Writing SQL: Can one record be returned by a sub-query?? if so how??

Question

Hello!

I am trying to write SQL code for my coursework using Access but it says "more than one record can not be returned by a sub query".

However the same code (query) works as long as it returns only one record.

Note I have to generate queries using SQL I am not allowed to use query builder.

Answer 1

You can choose for a query to return a specific row count...

SELECT TOP 1 * FROM Assignments

SELECT TOP 10 * FROM Assignments

etc.

Answer 2

Try to use joins to reduce sets rather than in the where clause and using sub queries.

I'm guessing you're using an "=" some where which is why you're getting the error in the sub-query.

if you need more specific help, feel free to e-mail me and I'll assist.

Answer 3

all records will be returned that matchyour query. use the word DISTINCT after SELECT.

ex.) SELECT DISTINCT Clients FROM dbo.Users WHERE 1=1 AND Name = Client

Answer 4

As stated above, use TOP 1:

SELECT * FROM Employees WHERE EmployeeID IN
(
SELECT TOP 1 EmployeeID FROM SalesData
WHERE SalesData.Something = something
)