SQL in MariaDB How to use data from 2 columns to select values from another column?

Question

The specific question is select rollno from class table who where present in dbms and not present in maths.

rollno sts sub
1      1    dbms
1      0    maths
2      1    dbms
2      0    maths
3      0    dbms
3      1    maths
4      0    dbms
4      0    maths
5      1    dbms
5      1    maths

Possible duplicate of [MySQL NOT IN from another column in the same table](http://stackoverflow.com/questions/14245671/mysql-not-in-from-another-column-in-the-same-table) — Shadow, Mar 06 '17 at 15:03
yes but it always returns the wrong value by this statement : select rollno from gaurav where sts=1 AND(sub='dbms' OR NOT(sub='maths')) — The Imaginary Guy, Mar 06 '17 at 15:05

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

You can use conditional aggregation for this:

select rollno
from mytable
group by rollno
having sum(sub = 'dbms' and sts = 1) > 0
   and sum(sub = 'maths' and sts = 1) = 0

This uses the fact that true evaluates to 1 and false evaluates to 0 in MySQL.

sum(sub = 'dbms' and sts = 1) finds number of rows where sub is dbms for the given rollno, which must be atleast 1
sum(sub = 'maths' and sts = 1) finds number of rows where sub is maths for the given rollno, which must be 0.

Demo

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 06 '17 at 14:59

Gurwinder Singh

38,557
6
51
76

this doesnot return any value, Please have a look at my table in the pic i have attached – The Imaginary Guy Mar 06 '17 at 16:20
@TheImaginaryGuy - When you say "present in dbms" do you mean has a row with sub as dbms and sts = 1? If so, updated the answer and added a demo as well – Gurwinder Singh Mar 06 '17 at 16:42
Yes I mean the same.Thanx for the demo.and one more thing is that i also want to display that the rollno's whose sts is 0 is maths – The Imaginary Guy Mar 07 '17 at 06:41
i mean it should print – The Imaginary Guy Mar 07 '17 at 06:44

score 0 · Answer 2 · answered Mar 06 '17 at 15:02

0

you could use two subselect with id and not in

select roolno
from my_table 
where sub in (select roolno from my_table  where sub ='dbms')
and sub not in (select roolno from my_table  where sub ='maths' )

answered Mar 06 '17 at 15:02

ScaisEdge

131,976
10
91
107

its is not returning any value – The Imaginary Guy Mar 06 '17 at 16:18
this mean that your data don't allow a proper result ..... where is your dara sample ? – ScaisEdge Mar 06 '17 at 16:21

SQL in MariaDB How to use data from 2 columns to select values from another column?

2 Answers2

Demo