how to get only values from single table

Sri Harsha 0
Viewed 32

I have only single table as shown below

id name product
1 sai m
2 sai EBB
3 sri m
4 ram m
5 sita EBB
6 siva EBB

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

id name product
3 sri m
4 ram m

only ebb

id name product
5 sita EBB
6 siva EBB

intersect or both

id name product
1 sai m
2 sai EBB

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

mysql-workbench join mysql sql-server sql
1 Answers

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)
Yoichi Nakayama0
Related