Subconsulta SQL

Asked

Viewed 85 times

-1

I’m doing the consultation below:

select idtce 
from tab_tce
where idempresa = 2493 
and YEAR(dt_inicial) = 2018
and idtce not in(
select TCE_ID
from SEC_VAGA_ESTUDANTE
)

But returns this error:

Msg 512, Level 16, State 1, Line 9
A subconsulta retornou mais de 1 valor. Isso não é permitido quando a subconsulta segue um =, !=, <, <= , >, >= ou quando ela é usada como uma expressão.

What’s wrong with my consultation?

sql sql-server query

  • Nothing wrong, just stating that has duplicated values in the query and as you are using equalities the bank can not list all information

  • I want to exclude from the main query the results that coincide with the idtce field in the sub nsulta, how can I do this?

  • You couldn’t do a Join instead of the sub-query, so you’d remove the replicates in Where.

  • this error happens when you return more than one value in the subquery, and in the where is using for example "=", with the in that shouldn’t happen because the in expects a list of values, the query is exactly the same as the one you put in the question?

  • two things you can do to solve: 1) replace the in by a not exists 2) Since the value is not in the other table, you can do for example a LEFT OUTER to validate this

  • I made an edit in the query. Now returns an empty list but at least it works.

  • Hi, friend did not give error. Your query is just returning duplicate values. I strongly advise you to make this query through a Join. Take a look at the link.

  • @Joãoignácio If the code has 8 lines how the error message is on line 9?

  • Simple, I didn’t start the query in line 1. Because I had other queries in the query.

Show 4 more comments

1 answer

2


I ended up doing so:

select * 
from tab_tce tce 
left join SEC_VAGA_ESTUDANTE sec
on tce.idtce = sec.TCE_ID
where YEAR(dt_inicial) = 2018
and idempresa = 2493
and sec.TCE_ID is null

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.