CODEWITHSUNDEEP
mysqlgroup-by
remyremy
remyremy

Reputation: 3758

Select query with filter at least one match

I have the following table which I'm trying to select data from using MySQL:

INSERT INTO `test` (`id_location`, `id_sector`, `id_group`) VALUES
(1, 1, 2),
(2, 1, 2),
(3, 1, 3),
(4, 3, 3),
(5, 2, 4),
(6, 3, 4),
(7, 2, 5),
(8, 1, 5),

I want to select the rows where id_sector = 1 but I also need to return any row with the same group value as returned by the query. For instance:

SELECT * FROM `test` WHERE `id_sector` = `1`

would return rows id_location 1, 2, 3 and 8 but I would be missing rows with id_location 4 and 7, which I need because there have the same id_group (3 and 7)

Upvotes: 0

Views: 19

Answers (1)

dportman
dportman

Reputation: 1109

You should first get a distinct list of id_groups that correspond to your selected id_sector and then query according to that list:

SELECT * FROM `test` WHERE `id_group` IN
    (SELECT DISTINCT `id_group` FROM `test` WHERE `id_sector` = 1)

Upvotes: 1

Related Questions