CODEWITHSUNDEEP
mysqlsql
log69
log69

Reputation: 83

MySQL query all distinct from column A where none of them has a specific value in column B

Could someone kindly point me to the right direction to seek further or give me any hints regarding the following task?

I'd like to list all the distinct values from a MySQL table from column A that don't have a specific value in column B. I mean none of the same A values have this specific value in B in any of there rows. Taking the following table (let this specific value be 1):

column A  | column B
----------------------
apple     |
apple     |
apple     | 1
banana    | anything
banana    |
lemon     |
lemon     | 1
orange    |

I'd like to get the following result:

banana
orange

Thanks.

Upvotes: 2

Views: 328

Answers (4)

Farrokh
Farrokh

Reputation: 1167

SELECT * FROM your_Table WHERE Column_A NOT IN(
    SELECT Column_A FROM Your_Table WHERE Column_B = '1'
)

Upvotes: 1

ngrashia
ngrashia

Reputation: 9904

Since there are null values, I have also added a nvl condition to column B .

ORACLE:

SELECT DISTINCT COLUMN_A  FROM MY_TABLE
WHERE COLUMN_A  NOT IN (SELECT COLUMN_A FROM MY_TABLE WHERE nvl(COLUMN_B,'dummy')  = '1');

MYSQL:

SELECT DISTINCT COLUMN_A  FROM MY_TABLE
WHERE COLUMN_A  NOT IN (SELECT COLUMN_A FROM MY_TABLE WHERE IFNULL(COLUMN_B,'dummy')  = '1');

Upvotes: 2

Jens
Jens

Reputation: 69460

This statement gives you the expected result:

select COLUMNA from myTable where COLUMNA not in (select distinct COLUMNA from myTable where columnB
=1) group by COLUMNA;

Upvotes: 1

Uriil
Uriil

Reputation: 12628

This might help you: 

SELECT DISTINCT A FROM MY_TABLE
WHERE A NOT IN (SELECT DISTINCT A FROM MY_TABLE WHERE B = 1)

Upvotes: 2

Related Questions