Last post May 05, 2016 04:07 PM by limno
May 05, 2016 07:34 AM|lolo512|LINK
i have 3 tables:
each user may apply to several vacancies and vice versa
table vacancy has a foreign key focalId.
where each vacancy is assigned to a focalUser(admin)
my query is supposed to get me the users that have applied for the vacancies of a specific focal.
This is what I have tried so far:
select * from [Users] where id= (select userId from [vac-user] where jobId=(select id from vacancy where focalId=1))
it returned this error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
please anyone can guide me?
May 05, 2016 09:42 AM|Weibo Zhang|LINK
Subquery returned more than 1 value.
This is not permitted
when the subquery follows =,
!=, <, <=
, >, >=
or when the subquery
is used as an expression.
According to the error message you could know that maybe there are more than one result comes from the two sub queries.
So, you’d better test the following two subqueries separately to make sure whether they are right or not.
query 1: select id from vacancy where focalId=1
query 2: select userId from [vac-user] where jobId=(select id from vacancy where focalId=1)
May 05, 2016 04:07 PM|limno|LINK
WHERE id IN (SELECT userId
WHERE jobId IN (SELECT id
WHERE focalId = 1))
FROM [Users] u
INNER JOIN [vac-user] vu
ON u.id = vu.userId
INNER JOIN vacancy v
ON v.id = vu.jobId
WHERE v.focalId = 1