Filter values based on multiple parameters in SQL (multiples rows)

Question

Thanks for helping! I'm a newbie in SQL.

I trying to retrieve users for whom the value of column offers__c is equal to 'a' but can also be equal to 'a' and 'w' and / or 'x' and / or 'y' and / or 'z' and / or empty ('').

Each user can have multiple rows (with 'a', 'w', 'x', 'y', 'z', '') with a non-unique common id. (They must have 'a' anyway.) I would like to retrieve the rows if they exist, but no others.

For example: if a user has multiples rows with a column equal to 'a' and (an other row with) 'w' and (an other row with) 'p', I do not want to retrieve it.

SELECT  customerAccount__c, 
        offers__c,
        Id
FROM Table1
WHERE customerAccount__c IN (
    SELECT customerAccount__c
    FROM Table1
    WHERE offers__c = 'a' OR ( offers__c = 'a' AND (
            offers__c = 'w' 
            OR offers__c = 'x' 
            OR offers__c = 'y'
            OR offers__c = 'z'
            OR offers__c = ''
            )
        )
)

With this query I also get value like 'p' or 'r'. I would like to exclude other values that do not match the one's I requested.

EDIT:

Table1:

customerAccount__c    offers__c         Id
        -                 -              -
        1                'a'        0015800001RzCebAAF
        1                'w'            ...
        1                'x'       
        3                'y'
        2                'a'
        2                'w'
        3                'z'
        3                'a'
        4                'a'
        5                'a'
        5                'w'
        5                'p'

OUTPUT:

customerAccount__c    offers__c         Id
        -                 -              -
        1                'a'      a0G5808300xWGxQEAG
        1                'w'      a0G5737300xWGxqEAG
        1                'x'      a0G5809990xWGxqEAG
        2                'a'            ...
        2                'w'
        3                'y'
        3                'z'
        3                'a'  
        4                'a'   
        5                'a'      0015800001RzCCbAAF
        5                'w'      0015800002RzDAbAAF
        5                'p'      0015800003REDEbAAF

For this exemple, I get offers__c (5) with 'p'. But I don't want it. I would like only the customerAccount__c (1) and exclude the (5) because of 'p'.

:note that I do not know all the different values in the table (because there is like 130+ potential values) eg. 'p', 'r', 'g' etc..

EXPECTED OUTPUT:

customerAccount__c    offers__c         Id
        -                 -              -
        1                'a'      
        1                'w'      
        1                'x'      
        2                'a'            ...
        2                'w'
        3                'y'
        3                'z'
        3                'a'  
        4                'a'

offers__c = 'a' OR ( offers__c = 'a' AND ( ... is redundant. a OR (a AND (b OR c OR d OR e)) is equivalent to a. — Cid
– Cid, Commented Feb 14, 2019 at 10:24
Mind giving us table structure with data sample and expected output ? Otherwise, we can't do tests — Cid
– Cid, Commented Feb 14, 2019 at 10:25
With your query you are going to get all rows where offers__c = a, the other bit after AND is irrelevant. I'm not sure how orders__c can be both 'a' and 'w', it can't be both. You need to clarify your question a bit, share your table structure, some sample data and what the expected output to be. — Dugggie
– Dugggie, Commented Feb 14, 2019 at 10:50

Laurenz Albe · Accepted Answer · 2019-02-15 08:59:01Z

1

The description is a bit unclear, but it sounds like:

SELECT * FROM table1 AS t
WHERE EXISTS
         (SELECT 1 FROM table1 AS t1
          WHERE t.customerAccount__c = t1.customerAccount__c
            AND t1.offers__c = 'a')
  AND NOT EXISTS
         (SELECT 1 FROM table1 AS t2
          WHERE t.customerAccount__c = t2.customerAccount__c
            AND t2.offers__c NOT IN ('a', 'w', 'x', 'y', 'z', ''));

To explain a bit, this selects all rows from the table that satisfy two conditions:

there is a row with the same customerAccount__c that has offers__c = 'a'
there is no row with the same customerAccount__c that has an offers__c different from 'a', 'w', 'x', 'y', 'z' and ''

edited Feb 15, 2019 at 8:59

answered Feb 14, 2019 at 11:36

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

pondia Over a year ago

I follow you on Github right now! O.M.G your answer is exactly what I need. Thanks you sooo much for your help. I'm so grateful 😁

pondia Over a year ago

``` AND NOT EXISTS (SELECT 1 FROM table1 AS t2 WHERE t.customerAccount__c = t2.customerAccount__c AND t2.offers__c NOT IN ('a', 'w', 'x', 'y', 'z', '')); ``` This part mean : exclude 'a', 'w', 'x' .. right ? Sorry for asking, I'm really a newbie in postgresql

Laurenz Albe Over a year ago

It means "and there does not exist anything (any row) in the table where the customerAccount__c is the same and it has an offers__c with a bad value".

pondia Over a year ago

Oh I see now. Thanks you ❤️

Collectives™ on Stack Overflow

Filter values based on multiple parameters in SQL (multiples rows)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related