CODEWITHSUNDEEP
mysqlsql
stighy
stighy

Reputation: 7170

How to get only row without a specific value in a column

this is a huge problem i've to solve in sql but i don't know how. This is my dataset: 

customer; publisher; qty

This is a data sample: 

CustA;  PublX;  10
CustA;  PublZ;  20
CustA;  PublF;  30
CustB;  PublX;   8
CustC;  PublD;   9 
CustD;  PublX;   9
CustD;  MyPub;  18
CustE;  PublZ;   3
CustE;  MyPub;   8

I need to do a Query that get ONLY Customer without "MyPubl" as publisher. Obviously i can't do :

SELECT * from myTable where Publisher <>"MyPubl"

One solution can be that i create a subset table that aggregate customer in one row like this: 

CustA; PublX PublZ PublF; 60
CustB; PublX; 8
etc...

Then with a INSTR i check if MyPub exists in second field ... This solution my work.. so i ask you How can i do this in SQL (aggregate 'same' customers in one row) ?

Any others suggestion (maybe more elegant) ?

Thanks

Upvotes: 0

Views: 59

Answers (3)

Strawberry
Strawberry

Reputation: 33945

Or old skool...

SELECT DISTINCT x.customer 
           FROM my_table x 
           LEFT 
           JOIN my_table y 
             ON y.customer = x.customer 
            AND y.publisher = 'MyPub' 
          WHERE y.customer IS NULL;

Upvotes: 0

h2ooooooo
h2ooooooo

Reputation: 39532

You can use NOT IN with a sub query:

SELECT
    customer,
    publisher,
    qty
FROM
    books
WHERE
    customer NOT IN (
        SELECT
            DISTINCT customer
        FROM
            books
        WHERE
            publisher = 'MyPub'
    )

SQL Fiddle Demo

Which will output:

CUSTOMER | PUBLISHER | QTY
---------+-----------+-----
CustA    | PublZ     |  20
CustA    | PublF     |  30
CustB    | PublX     |   8
CustC    | PublD     |   9

Upvotes: 1

Colm Prunty
Colm Prunty

Reputation: 1620

Maybe this:

SELECT * FROM myTable 
WHERE customer NOT IN (SELECT customer FROM myTable WHERE Publisher = "MyPubl")

Or if you just want the customers

SELECT DISTINCT customer FROM myTable

Upvotes: 1

Related Questions