CODEWITHSUNDEEP
sqlsqlitedb-browser-sqlite
Vij
Vij

Reputation: 59

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.

Upvotes: 2

Views: 68

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If what you want are the positive people who are at a location where there is also someone who is not tested, you might consider:

select ch.LocID,
       group_concat(case when t.results = 'positive' then ch.PersonID end) as positive_persons
from checkin ch left join
     testing t
     on ch.personid = t.personid 
group by ch.LocId
having sum(case when t.results = 'positive' then 1 else 0 end) > 0 and
       count(*) <> count(t.personid);  -- at least one person not tested

With this structure, you can get the untested people using:

group_concat(case when t.personid is null then ch.personid)

Upvotes: 2

forpas
forpas

Reputation: 164099

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

Upvotes: 2

Radim Bača
Radim Bača

Reputation: 10701

You have several mistakes (missing exists, independent subquery in exists). I believe that this should do the work

select ch1.LocID, ch1.PersonID 
from checkin ch1
join testing t1 on ch1.personid = t1.personid 
where results = 'Positive'
and exists (
    select 1
    from checkin ch2    
    where ch1.LocID = ch2.LocID and ch2.PersonID not in (
        select testing.PersonID 
        from testing
    )
);

Upvotes: 1

Related Questions