Search code examples
sqlsqlitedb-browser-sqlite

Nesting queries

My query from the attached schema is asking me to look for the same location of where the people who tested positive went and were in the same people as the untested people. (Untested means the people not there in the testing table.

Schema

--find the same locations of where the positive people and the untested people went  

select checkin.LocID, checkin.PersonID 
from checkin join testing on checkin.personid = testing.personid 
where results = 'Positive'
and  (select CheckIn.PersonID  
from checkin join testing on checkin.PersonID = testing.PersonID where CheckIn.PersonID
not in (select testing.PersonID from testing));

In my view the query is stating the following

To select a location and person from joining the checking and testing table and the result is positive and to select a person from the check in table who is not there in the testing table.

Since the answer I am getting is zero and I know manually there are people. What am I doing wrong?

I hope this makes sense.

Solution

  • You can get the people tested 'Positive' with this query:

    select personid from testing where results = 'Positive'

    and the untested people with:

    select p.personid 
from person p left join testing t 
on t.personid = p.personid
where t.testingid is null

    You must join to each of these queries a copy of checkin and these copies joined together:

    select l.*
from (select personid from testing where results = 'Positive') p
inner join checkin cp on cp.personid = p.personid
inner join checkin cu on cu.lid = cp.lid
inner join (
  select p.personid 
  from person p left join testing t 
  on t.personid = p.personid
  where t.testingid is null
) pu on pu.personid = cu.personid
inner join location l on l.locationid = cu.lid