Thanks, I did try left joins, but no data returned. RBS
On Sun, Sep 22, 2019 at 12:22 PM Richard Damon <[email protected]> wrote: > 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 > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
