convert nested query to join

Question

I have two tables:

entry
    id
    ......

individual
    id,
    entry_id,
    code

where entry has a one to many relation to individual.

I want to select all individuals that belong to an entry that contains more than 3 individuals which have code between A=10 and B=15

I wrote this query and it works:

select entry_id,id 
from individual as i 
where i.entry_id in 
  (select entry_id 
  from individual as v 
  where v.code between 10 and 15 
  group by entry_id 
  having count(*) > 3 )

but it's slow.

So I want to try to convert it to use a join instead of a nested query.

I think it's not possible *without* subquery. There are different solutions for sure... — Stefan Steinegger, Jun 29 '15 at 10:58
I make index for the code and index for entry_id but still slow — Ibraheem, Jun 29 '15 at 11:00
don't know what RDMS you're using, but T-SQL has various tools to help suggest useful indexes. I assume other databases have similar functions. You may be able to use them to target your indexes better. — Bob Tway, Jun 29 '15 at 11:00
Change the IN to Exists, select entry_id,id from individual as i where Exists (select entry_id from individual as v where v.code between 10 and 15 and i.entry_id = v.entry_id group by entry_id having count(*) > 3 ) — Mike Miller, Jun 29 '15 at 11:04
You are saying "... an entry that contains more than 3 individuals and has code between ...", but in your definition the code is an attribute of individual, not of entry. Could you explain better your request, please? — Renzo, Jun 29 '15 at 11:05
@Renzo: sorry, this was may edit. I tried to fix grammar messed it up. Just found it myself and fixed it. Now it should be clear. — Stefan Steinegger, Jun 29 '15 at 11:14
@Renzo 3 individuals has code between .... the individuals has the code between .... — Ibraheem, Jun 29 '15 at 11:19
How many records thare are in the two tables? And your query is always with the two specific codes, or they change? How many different values of code do you have? — Renzo, Jun 29 '15 at 11:43

score 3 · Accepted Answer · answered Jun 29 '15 at 12:59

This is a join version, but I'm not sure if it will be faster than your nested query solution.

select i1.entry_id, i1.id
from individuals as i1 
    join individuals as i2 
        on (i1.entry_id = i2.entry_id)
where i2.vcode between 10 and 15
group by i1.entry_id, i1.id
having count(*) > 3;

Note that this query is only equivalent to your query if id or (id, entry_id) are primary/unique key for table individuals.

score 1 · Answer 2 · answered Jun 29 '15 at 13:11

select 
    entry_id, 
    id,
    code
from 
    individuals as i1
where 
    vcode between 10 and 15
    And entry_id in (
        select entry_id from individuals group by entry_id having count(entry_id) > 3
    )

Join Entry table only if you need to display value from Entry table

convert nested query to join

2 Answers2