mysql select result based on multiple matching criteria of 2 or more columns

Question

My table is like this:

CREATE TABLE candidate_tbl  (
  `name` VARCHAR(1),
  `degree` VARCHAR(41),
  `doneMasters` VARCHAR(6)
);

INSERT INTO candidate_tbl 
  (`name`, `degree`, `doneMasters`)
VALUES
  ('A', 'MBA', 'true'),
  ('A', 'MS', 'false'),
  ('B', 'MBA', 'true'),
  ('B', 'MS', 'true'),
  ('C', 'MBA', 'false'),
  ('C', 'MS', 'false'),
  ('D', 'MBA', 'false'),
  ('D', 'MS','true'),
  ('E', 'MBA', 'false'),
  ('E', 'MS', 'false'),
  ('F', 'MBA', 'false'),
  ('F', 'MS', 'true'),
  ('G', 'MBA', 'false'),
  ('G', 'MS', 'false'),
  ('H', 'MS', 'true'),
  ('H', 'MS', 'true');

I want the list of candidates who neither has MS nor MBA, which is C,E and G

I tried these 4 ways, Fiddle given here

Method-1

SELECT name,count(*) FROM candidate_tbl
WHERE doneMasters = 'false'
AND doneMasters = 'false'
GROUP BY name
HAVING count(*) = 2;

Method-2

SELECT DISTINCT name FROM candidate_tbl ct1 WHERE NOT EXISTS
(
SELECT name FROM candidate_tbl ct2 WHERE ct1.name = ct2.name and doneMasters = 'true'
);

Method-3

SELECT name FROM (SELECT name,group_concat(doneMasters) as dmf FROM candidate_tbl
GROUP BY name) dd
WHERE dmf = 'false,false';

Method-4

SELECT name FROM (SELECT name, group_concat(doneMasters) as dmf FROM candidate_tbl
GROUP BY name) dd
WHERE dmf = 'false,false';

I want to know which is the best solution in terms of performance and accuracy, or other alternate solutions.

You need to test the results on your system and on your data. — Gordon Linoff, Aug 25 '20 at 10:55
If you start storing all the things a candidate hasn't done, this is going to be a HUGE dataset — Strawberry, Aug 25 '20 at 11:02
Note that queries against views in MySQL are unlikely to be efficient. — Strawberry, Aug 25 '20 at 12:28

score 3 · Answer 1 · answered Aug 25 '20 at 11:00

Your data model doesn't really make sense to me. Why store negative information in a table, for instance?

The best way would be to normalize the data model to have a separate table of candidates and their degrees. Then use not exists:

select c.*
from candidates c
where not exists (select 1
                  from candidate_degrees cd
                  where cd.candidate_id = c.candidate_id and
                        cd.degree in ('MS', 'MBA')
                 );

This would be a rather dramatic change to your data model:

The degrees table would only have degrees that exist (and perhaps other information such as the year, school, and so on).
The candidates would be identified by an id that can be used for joining.

agree with you, actually this is a view where unwanted data is filtered from a table and only this much info is relevant for me — mysqlrockstar, Aug 25 '20 at 11:23

score 1 · Answer 2 · answered Aug 25 '20 at 11:03

1

Try this.

SELECT name FROM candidate_tbl 
WHERE doneMasters = 'false' 
AND degree in ('MBA', 'MS') 
GROUP BY name HAVING count(1) = 2;

DEMO HERE: DB-FIDDLE

answered Aug 25 '20 at 11:03

Vivek Jain

2,730
6
12
27

score 0 · Answer 3 · answered Aug 25 '20 at 12:56

0

the solution is simply

SELECT name FROM candidate_tbl 
WHERE degree in ('MBA', 'MS') AND doneMasters = 'false' 
GROUP BY name;

it will return a unique name from list who has not done MBA or MS.

answered Aug 25 '20 at 12:56

Maulik Patel

63
6

score 0 · Answer 4 · answered Aug 25 '20 at 13:12

If for each name there are always exactly 2 rows with values 'MBA' and 'MS' in the column degree, then there is no reason to check or filter the column degree.
The simplest way is to group by name, filter out the rows with true in the column doneMasters and check in the HAVING clause if in the results there are 2 rows (these 2 rows will contain 'false' in the column doneMasters):

SELECT name
FROM candidate_tbl
WHERE doneMasters = 'false'
GROUP BY name
HAVING COUNT(*) = 2

See the demo.
Results:

> | name |
> | :--- |
> | C    |
> | E    |
> | G    |

Note that a flag column like doneMasters would be better (for clarity, coding simplicity, less storage space and better performance) defined as BOOLEAN. See the demo.

Right, If for each name there are always exactly 2 rows with values 'MBA' and 'MS' in the column degree — mysqlrockstar, Aug 25 '20 at 13:17

mysql select result based on multiple matching criteria of 2 or more columns

4 Answers4

Linked

Related