Convert a query from MySql to SQLite

Question

Please help to change the below MySQL query to SQLite

SELECT 
    DISTINCT t.arabic_term AS t__1,
    SUBSTR(t.definition, 1, 60) AS Definition,
    Sum(if(t3.is_main="y", 1 ,0)) AS t3__5
FROM term t 
INNER JOIN term_disease t2 ON (t2.disease_id = t.term_id) 
INNER JOIN term_disease_symptom t3 ON (t3.disease_id = t2.disease_id)
WHERE (t3.symptom_id in ( 45378 , 45351, 5546 ) AND t2.gender != 1  AND t2.active = 1 )
GROUP BY t3.disease_id
ORDER BY t3__5  desc;

Yaqub Ahmad · Accepted Answer · 2012-09-27 08:55:37Z

1

Below

Sum(if(t3.is_main="y", 1 ,0)) AS t3__5

sould be replaced with

SUM(CASE t3.is_main WHEN 'y' THEN 1 ELSE 0 END) AS t3__5

answered Sep 27, 2012 at 8:55

Yaqub Ahmad

27.7k24 gold badges105 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CL. · Accepted Answer · 2012-09-27 08:32:16Z

1

SQLite does not have an IF function, but boolean expression are defined as having the values 1 or 0. So that particular expression would be:

SUM(t3.is_main = 'y') AS t3__5

Anything else is standard SQL.

answered Sep 27, 2012 at 8:32

CL.

182k18 gold badges241 silver badges282 bronze badges

1 Comment

CL. Over a year ago

In SQLite, the expression t3.is_main = 'y' already is 0 or 1.

Collectives™ on Stack Overflow

Convert a query from MySql to SQLite

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related