Nesting queries

Question

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.

--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.

What results do you want? – Gordon Linoff Aug 20 '20 at 11:07 — Gordon Linoff, Aug 20 '20 at 11:07

score 2 · Accepted Answer · answered Aug 20 '20 at 10:52

2

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

answered Aug 20 '20 at 10:52

forpas

160,666
10
38
76

Thank you. Worked and gave me the location. If i had to find the personid also, I am presuming I would have to put ` checkin.personid `next to select? – Vij Aug 20 '20 at 11:37
Another question is why do you put the 1.* and not the (*) alone. What does the 1 signify? – Vij Aug 20 '20 at 11:38
2

`l` (not 1) is the alias of the table `location`. If you also want persons you can add `p.*` for the infected persons or `pu.*` for the untested. – forpas Aug 20 '20 at 15:05

Gordon Linoff · Answer 2 · 2020-08-20T14:25:25.283

2

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)

edited Aug 20 '20 at 14:25

answered Aug 20 '20 at 11:10

Gordon Linoff

1,242,037
58
646
786

1

Thank you, Yes I was looking for the people who are positive and were at the same place as the people who were untested. – Vij Aug 20 '20 at 11:35

score 1 · Answer 3 · answered Aug 20 '20 at 10:48

1

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
    )
);

answered Aug 20 '20 at 10:48

Radim Bača

10,646
1
19
33

Thank you. But it gave me the results of the person who is positive only and I was looking for the person who are positive and were at the same place as people who were untested. Thank you for your help. – Vij Aug 20 '20 at 11:35

Nesting queries

3 Answers3