CODEWITHSUNDEEP
sqlselect
Mike
Mike

Reputation: 2339

How do you use SQL MINUS statement with multiple returns?

I have a SQL statement that I want to return 6 fields found from a MINUS statement that only compares 1 field from 2 tables. It works properly when the MINUS statement only returns 1 entry, but errors if it returns more than 1.

SELECT DROPPER_ID, EMAIL, ACTIVE, COUNTRY_CD, FIRST_NAME, LAST_NAME FROM PETE.DROPPER   
WHERE DROPPER_ID = (
    SELECT DROPPER_ID FROM PETE.DROPPER WHERE COUNTRY_CD <> 'USA' AND ACTIVE = 1 
    MINUS 
    SELECT DROPPER_ID FROM PETE.DROPPER_COMPARE);

How can I accomplish this?

Upvotes: 1

Views: 5925

Answers (2)

David Marx
David Marx

Reputation: 8558

The problem is in your where clause: it is currently designed to compare dropper_id with a single value, but that's not actually what you want. To fix it, replace the '=' symbol with the word 'in'. This will tell the WHERE clause to treat the subquery as a tuple or list of values instead of a single value.

Upvotes: 0

Tom H
Tom H

Reputation: 47392

Instead of using =, try using the IN statement:

SELECT DROPPER_ID, EMAIL, ACTIVE, COUNTRY_CD, FIRST_NAME, LAST_NAME FROM PETE.DROPPER   
WHERE DROPPER_ID IN (
    SELECT DROPPER_ID FROM PETE.DROPPER WHERE COUNTRY_CD <> 'USA' AND ACTIVE = 1 
    MINUS 
    SELECT DROPPER_ID FROM PETE.DROPPER_COMPARE);

Upvotes: 1

Related Questions