TransWikia.com

Query output conditional upon values in dataset

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?

One Answer

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP