Well, sorry but I don’t know how to formulate the subject correctly. That’s why I’d better describe the issue.
I have a kind of following table:
CREATE TABLE IF NOT EXISTS `Test` ( `Id` INT NOT NULL AUTO_INCREMENT, `Name` VARCHAR(5) NOT NULL, `v1` INT NOT NULL, PRIMARY KEY (`Id`) ); INSERT INTO `Test` (`Name`, `v1`) VALUES ('A', 4), ('A', 3), ('B', 6), ('C', 1), ('C', 2), ('C', 7), ('D', 7), ('D', 5), ('E', 3), ('F', 1), ('F', 2), ('G', 7), ('H', 9), ('H', 1), ('I', 5);
What I need is to find all names that matches e.g. 7.
And now I need to select all rows except
Resulting table is supposed to be:
=============== | Name | v1 | |–––––––––––––| | A | 4 | | A | 3 | | B | 6 | | E | 3 | | F | 1 | | F | 2 | | H | 9 | | H | 1 | | I | 5 | |–––––––––––––|
I’ve tried the query:
select t1.Name, t1.v1 from `Test` as t1 join ( select Name from `Test` where v1 = 7 ) as t2 on t1.Name != t2.Name
but only realized that I still do not understand the mechanics of
Hope for some help with this issue.
Thanks in advance.
SELECT Name FROM Test WHERE v1 = 7
returns all the
Names that you want to exclude.
Use it with the operator
SELECT * FROM Test WHERE Name NOT IN (SELECT Name FROM Test WHERE v1 = 7)
See the demo.
> Id | Name | v1 > -: | :--- | -: > 1 | A | 4 > 2 | A | 3 > 3 | B | 6 > 9 | E | 3 > 10 | F | 1 > 11 | F | 2 > 13 | H | 9 > 14 | H | 1 > 15 | I | 5