Subquery returned more than 1 value

[ddot02]

Hello,
I’m building a Job Searching Site. There’s a member Area that divided into two Membership and Pages. The 1st one is for Candidates (who looks for Jobs), and the other one is for Institutions who looks for Employees (I’ll call them as Client)

I made some relational tables in the site, but the most important is the 3 tables:
Table Clients
Table Candidates
Table Jobs

I used a session variable to indicate the specific client who enters the page.

When a Client signs in, he will enter member area for Clients. At the main page, he will find two lists (as tables) displayed there,

1st List
It will display the list of latest Candidates who looking for Job.
I don’t have any problem displaying this one becoz it just needs a simple query such as
“SELECT TOP 10 FROM Candidates Order by RegistrationDate Desc”

2nd List
This table displays list of
“Candidates who related with Your Opening Job Category”

I got a problem for this one. One Client possibly has multiple Opening Job Categories, for example: a Client is looking for a driver (category is Transportation) and he’s also looking for an Office Secretary (category is Secretary).

The basic way will be

“SELECT * from Candidates WHERE Category = ‘Transportation’ AND Category = ‘Secretary’”

But we can not implement that simple in ASP. It must indicate the Categories for each Client.

I tried this one but failed because I used a variable that returns more than one value

“SELECT * FROM Candidates WHERE Category = (SELECT Category FROM Jobs WHERE InstitutionID = ‘client’)”

* ’client’ is a variable I took from a user session

In this case, it will return an error if the client has opened 2 job positions that have two difference Categories (Transportation and Secretary)
An Error message will appears and say:

In SQL Query Analyzer :
“Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”

In Browser using ASP:
[Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have no idea to solve this problem.
How to retrieve the records when we use a variable (for the conditions) that returns 2 values inside.

Anyone can help?
before, I’d like to say Thank You

 

[Lothario]

OK let me first correct some of your statements...

The basic way will be

“SELECT * from Candidates WHERE Category = ‘Transportation’ AND Category = ‘Secretary’”

this query is wrong because if a client is looking for a driver and also for a secretary then it should be

“SELECT * from Candidates WHERE Category = ‘Transportation’ OR Category = ‘Secretary’”

But we can not implement that simple in ASP.

Why not?

I tried this one but failed because I used a variable that returns more than one value

“SELECT * FROM Candidates WHERE Category = (SELECT Category FROM Jobs WHERE InstitutionID = ‘client’)”

Try this one:

“SELECT * FROM Candidates WHERE Category IN (SELECT Category FROM Jobs WHERE InstitutionID = ‘client’)”

-L

 

[ddot02]

Thanks for the reply Lothario, Its done now...

well i did a mistake...
I did, I have tried IN, SOME, ANY and ALL attributes before, but it always returns one record only (it should be two)...
- IN, SOME and ANY will return one record
- ALL will return no record instead...

But after you told me that, i became curious...
I tried again and still got the same result...
Then i checked the records... I found something in the value in my Category Field: "Office Secretary"... I removed the "Office" word and left the "Secretary" alone, after that it works well using IN.
So, we can not use "multiple words value" in a field for this purpose?

Thank You Very Much

Heddy