On 9/22/19 6:10 AM, Bart Smissaert wrote:
> Have this query:
>
> SELECT round(((Julianday('now') - 2415018.5) - P.DOB) / 365.25, 0) AS AGE,
> AVG(BM.numeric_value) AS avg_Hb_Male,
> AVG(BF.numeric_value) as avg_Hb_Female
> FROM PATIENTS P INNER JOIN NUM_VALUES BM ON(P.ID = BM.ID AND P.SEX = 'Male')
> INNER join NUM_VALUES BF ON(P.ID = BF.ID AND P.SEX =
> 'Female')
> WHERE BF.term_text = 'Haemoglobin estimation' and
> BM.term_text = 'Haemoglobin estimation'
> GROUP BY AGE
> HAVING AGE > 18 and AGE < 98
> ORDER BY AGE ASC
>
> Which always used to run fine and return rows. There is no error, but there
> are no returned rows. I have checked all the where conditions and they are
> all fine.
> When I leave out either of the NUM_VALUES joins (so either male or female
> patients) then it
> runs fine and returns data as expected.
>
> Something like this runs fine as well and returns the right data:
>
> select p.sex, round(avg(n.numeric_value), 2) as avg_Hb
> from patients p inner join num_values n on(p.id = n.id)
> where n.term_text = 'Haemoglobin estimation'
> group by p.sex
>
> Any idea what could be the problem here?
> Running version 3.22.0.
>
> RBS
If I am reading that right, the only way that query should have worked
at all is if INNER JOIN wasn't being supported and had been converted to
a LEFT JOIN.
INNER JOIN only returns records that match BOTH tables, so to pass the
first INNER JOIN the record need P.SEX = 'Male', and then to pass the
second it would need
P.SEX = 'Female' at the same time.
--
Richard Damon
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
