CODEWITHSUNDEEP
sql
Ali
Ali

Reputation: 13

Finding the Unique values in column against row

I have just started learning SQL , what i want is to write a query to get only the Column 2 value which doesn't have "D" present against them in Column 1.

For eg in the case below is "AC" 

Column1 Column 2
A       AB
B       AB
C       AB
D       AB
A       AB
B       AB
C       AB
D       AB
A       AB
B       AB
A       AC
B       AC
C       AC
A       AC
B       AC
C       AC
A       AC
B       AC
C       AC

Upvotes: 1

Views: 53

Answers (2)

Marvin
Marvin

Reputation: 14415

See this SQL fiddle based on MySQL 5.6:

SELECT DISTINCT column2
FROM table_name
WHERE column2 NOT IN
(
    SELECT column2
    FROM table_name
    WHERE column1 = 'D'
);

There are basically two queries: the inner query fetches all column2 values which have a corresponding column1 with value D; the outer query fetches all column2 values from your original table excluding the ones found by the inner query.

Data:

column1     column2
A           AB
B           AB
C           AB
D           AB
A           AC
B           AC
C           AC
A           AD
D           AD
D           AD
A           AE

Output:

column2
AC
AE

Upvotes: 0

Bhushan
Bhushan

Reputation: 6181

Try This:

select column1, coulmn2 from table_name where column1 != 'D'

column1 != 'D' removes the records from result which contains D in column1.

Similarly if you want to ignore more values from column1 you can use not in.

select column1, coulmn2 from table_name where column1 not in ('D','A')

Update1

To get count of records which doesn't have 'D' in column1 you can use:

select count(*) from table_name where column1 != 'D'

Upvotes: 1

Related Questions