Database Administrators Asked on December 10, 2021
If the number
column contains values 5 AND 6, AND the value X exists in the chr
column, I would like to exclude rows with 10 in the test
column.
But, if the number
column contains values 4 AND 5, AND the value X does not exist in the chr
column, I would like to exclude all but ONE of the rows with 10 in the test
column.
+----+------+-----+--------+
| id | test | chr | number |
+----+------+-----+--------+
| 1 | 7 | C | 4 |
| 2 | 7 | T | 5 |
| 3 | 8 | C | 4 |
| 4 | 8 | T | 5 |
| 5 | 9 | A | 4 |
| 6 | 9 | G | 5 |
| 7 | 10 | T | 4 |
| 8 | 10 | A | 5 |
| 9 | 10 | X | 6 |
| 10 | 14 | T | 4 |
| 11 | 14 | G | 5 |
+----+------+-----+--------+
How is it possible to do in MySQL?
I am pretending your table name is number_table. You query can be divided into two separate ones -- the first excludes rows with 10 in the test column and the second includes only one row with 10 in the test column. I assume the two queries are independent. From interpreting your question, I believe this query will do the job.
SELECT * FROM number_table WHERE
(EXISTS(SELECT * FROM number_table WHERE number = 5 OR number = 6) AND EXISTS(SELECT chr FROM number_table WHERE chr = X) AND number <> 10)
UNION
SELECT * FROM number_table WHERE
((EXISTS(SELECT * FROM number_table WHERE number = 4 OR number = 5))
AND NOT EXISTS(SELECT chr FROM number_table WHERE chr = X) AND number = 10
) LIMIT 1;
Answered by YardGlassOfCode on December 10, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP