CODEWITHSUNDEEP
sqldatabasejoinsubquery
Ibraheem
Ibraheem

Reputation: 100

convert nested query to join

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.

Upvotes: 2

Views: 2407

Answers (2)

Emipro Technologies Pvt. Ltd.
Emipro Technologies Pvt. Ltd.

Reputation: 14746

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

Upvotes: 1

acesargl
acesargl

Reputation: 569

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.

Upvotes: 3

Related Questions