funbinod Posted May 15, 2014 Share Posted May 15, 2014 i'm trying to select columns with multiple conditions. i want to select all 'tr_type' for all 'aid's but not 'SALES' & 'PURCHASE' for aid in '1' & '2'. i tried --- SELECT * FROM tran WHERE aid NOT IN ('1','2') AND tr_type IN ('SALES','PURCHASE') ORDER BY date it didn't work. please suggest.... Link to comment Share on other sites More sharing options...
birbal Posted May 15, 2014 Share Posted May 15, 2014 (edited) SELECT * FROM tran WHERE NOT IN(SELECT aid FROM tran WHERE aid IN ('1','2') AND tr_type IN ('SALES','PURCHASE')) Edited May 15, 2014 by birbal Link to comment Share on other sites More sharing options...
funbinod Posted May 15, 2014 Author Share Posted May 15, 2014 u r right but one--- i want all columns for all 'aid's but for 'aid' 1 & 2 i don't want 'sales' & 'purchase'. and 'sales' & 'purchase' are not column name. they are row value for 'tr_type' column & this column has other values also - 'receipt', 'payment' etc.... lets assume with this |---|------------|--------|---------||aid| date | amt | tr_type ||---|------------|--------|---------|| 1 | 2014-05-01 | 50000 | SALES || 2 | 2014-05-02 | 50000 |PURCHASE ||---|------------|--------|---------|| 1 | 2014-05-03 | 50000 | RECEIPT || 2 | 2014-05-04 | 50000 | PAYMENT || 3 | 2014-05-05 | 50000 | SALES || 3 | 2014-05-06 | 50000 |PURCHASE ||---|------------|--------|---------| in this table i dont want first 2 rows only. Link to comment Share on other sites More sharing options...
funbinod Posted May 15, 2014 Author Share Posted May 15, 2014 SELECT * FROM tran WHERE NOT IN(SELECT aid FROM tran WHERE aid IN ('1','2') AND tr_type IN ('SALES','PURCHASE')) thanks. but its not still selecting aid 1 & 2.... Link to comment Share on other sites More sharing options...
birbal Posted May 15, 2014 Share Posted May 15, 2014 (edited) you can use union SELECT * FROM tran WHERE aid IN('1','2') AND tr_type NOT IN ('SALES','PURCHASE'))UNIONSELECT * FROM tran WHERE aid NOT IN('1','2') Edited May 15, 2014 by birbal Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now