I have only single table as shown below

please help me to write a SQL query to show only m , only ebb and both . only m

only ebb

intersect or both

i have tried using not except, joins but nothing worked for me.Please help me .

CodePudding user response：

Use subquery as follows

mysql> select * from tbl where name in (select name from tbl where product = 'm') and name not in (select name from tbl where product = 'EBB');
 ---- ------ --------- 
| id | name | product |
 ---- ------ --------- 
|  3 | sri  | m       |
|  4 | ram  | m       |
 ---- ------ --------- 
2 rows in set (0.01 sec)

mysql> select * from tbl where name not in (select name from tbl where product = 'm') and name in (select name from tbl where product = 'EBB');
 ---- ------ --------- 
| id | name | product |
 ---- ------ --------- 
|  5 | sita | EBB     |
|  6 | siva | EBB     |
 ---- ------ --------- 
2 rows in set (0.01 sec)

mysql> select * from tbl where name in (select name from tbl where product = 'm') and name in (select name from tbl where product = 'EBB');
 ---- ------ --------- 
| id | name | product |
 ---- ------ --------- 
|  1 | sai  | m       |
|  2 | sai  | EBB     |
 ---- ------ --------- 
2 rows in set (0.00 sec)