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