return the rows by family with sql

This is my first question in stackoverflow so be nice to me 🙂

i have a table which has a column family, when i make a query (with a where statment) i want to show the results as a group of rows with the same family

for example the table is shop:

+----+--------+-------+
| id | family | money |
+----+--------+-------+
|  1 |      1 |   100 |
|  2 |      1 |    70 |
|  3 |      2 |    10 |
|  4 |      2 |    20 |
|  5 |      3 |    50 |
+----+--------+-------+

So i want when executing a query like this :

SELECT * FROM shop where money=100 --(adding to the query of course what's needed)

it will select these two :

+-----+----------+---------+
| id  |  family  |  money  |
+-----+----------+---------+
| 1   |       1  |    100  |
| 2   |       1  |     70  |
+-----+----------+---------+

Answer

You can use in or exists:

SELECT s.*
FROM shop 
WHERE s.family IN (SELECT s2.family FROM shop s2 WHERE s2.money = 100);

Advertisement

Answer