CODEWITHSUNDEEP
sqlsqlite
Hendrik
Hendrik

Reputation: 47

SQL sub-select returns 2 columns - expected 1

sqlite> SELECT *
   ...> FROM Studio
   ...> WHERE third_party_id NOT IN (
   ...> SELECT third_party_id,effeciency_rating
   ...> FROM Staff
   ...> WHERE Staff.third_party_id = Studio.third_party_id AND
   ...> Staff.effeciency_rating < 0.7
   ...> );

with the error message

sub-select returns 2 columns - expected 1

So I know why this happens, my subselct has thirdpartyid and effeciency rating while I only select thirdpartyid from Studio.

So my relation: Stuido employs staff So now I want to select all studios that didnt employ a staff with effeciency rating under 0.7.

So my question is how do I select my staff based on effeciency rating and give my studio back?

Upvotes: 2

Views: 17920

Answers (3)

Claire
Claire

Reputation: 454

Change:

...> SELECT third_party_id,effeciency_rating

To:

...> SELECT third_party_id

Upvotes: 0

Nadav Lev
Nadav Lev

Reputation: 76

Your inner query has 2 columns defined

third_party_id,effeciency_rating

remove the effeciency_rating column from the inner select.

See example and tutorial

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You would just do:

SELECT st.*
FROM Studio st
WHERE st.third_party_id NOT IN (SELECT s.third_party_id
                                FROM Staff s
                                WHERE s.third_party_id = st.third_party_id AND s.effeciency_rating < 0.7
                               );

Note that I introduced table aliases and qualified all the column names so the query is unambiguous. However, as written, the query doesn't really make sense. I suspect that you want NOT EXISTS:

SELECT st.*
FROM Studio st
WHERE NOT EXISTS (SELECT s.third_party_id
                  FROM Staff s
                  WHERE s.third_party_id = st.third_party_id AND s.effeciency_rating < 0.7
                 );

In any case, I recommend NOT EXISTS over NOT IN because it handles NULL values more sensibly.

Upvotes: 3

Related Questions