CODEWITHSUNDEEP
sqlselectsqlitesimplify
Kevin McFadden
Kevin McFadden

Reputation: 357

SQLITE3 compound "Select" statement

I have a table with several columns. Columns 1and 2 can contain any of four alpha values: set={A,B,C, D}.

I want to check if each column to see if they contain either of two values from the set. So I want to simplify this statement:

SELECT * FROM MUTATION-TABLE WHERE (COLUMN1 = "A") OR (COLUMN2 = "A") OR (COLUMN3 = "A")
 OR (COLUMN1 = "B") OR (COLUMN2 = "B") OR (COLUMN3 = "B").

Is there a way to simplify this statement. As I add more columns, there will be more mutations to check. I can see the SQL statement getting long/repetitive.

Upvotes: 1

Views: 2159

Answers (3)

mu is too short
mu is too short

Reputation: 434665

If you only have two values to but lots of columns, then switch to IN and check for your constant values in a set of columns:

SELECT *
FROM "MUTATION-TABLE"
WHERE 'A' IN (COLUMN1, COLUMN2, COLUMN3)
   OR 'B' IN (COLUMN1, COLUMN2, COLUMN3)

And with more columns:

SELECT *
FROM "MUTATION-TABLE"
WHERE 'A' IN (COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5)
   OR 'B' IN (COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5)

The queries should be the same as far as the database is concerned but this version might be more readable.

Upvotes: 3

Gerald P. Wright
Gerald P. Wright

Reputation: 796

All other things being equal, I think I would modify the DB structure to eliminate the multiple columns. While I understand that this might be a bit of work up front, it will very significantly reduce the complexity of your SQL. The fact that you are already considering adding more columns for additional mutations only reinforces my thoughts that this is the proper direction.

Example - assuming that your existing primary key is patientID.

new table - existing-mutations patientID - PK mutationType - char

The SQL then becomes

SELECT *
FROM MUTATION-TABLE
WHERE patientID IN (SELECT patienID 
                    FROM existing-mutations 
                    WHERE mutationType = "A")

I hope this helps.

Upvotes: 0

Adam Wagner
Adam Wagner

Reputation: 16107

You can use a in in your where clause, instead of a direct equality check. Example:

SELECT
    *
FROM
    mytable
WHERE
    col1 IN ("A", "B")
    OR col2 IN ("A", "B")
    OR col3 IN ("A", "B")

Upvotes: 1

Related Questions