CODEWITHSUNDEEP
sqlmysql
Gaurav
Gaurav

Reputation: 691

Change this query with inner joins?

I have been having some trouble working with inner joins I guess:

select count(distinct id) 
  from svn1,
       svn2 
 where svn1.revno = svn2.revno 
   and svn1.type = 'Bug' 
   and svn2.authors IN (select authors 
                          from svn2 
                         where revno = '33')

How to make this faster with inner joins? My query with inner joins gives weird results.

Table Info for svn1: Columns: id revno type Data:
1 22 Bug
1 23 Change
1 24 Bug
2 33 Bug
2 34 Bug

Table Info for svn2: Columns: revno authors Data:
22 A
22 B
22 C
33 D
33 A
33 C

I want ids of type bug which have a common author with authors of revno 33. i.e ids which also have revno with authors A,D or C in it

In general I also want a query to answer give an id find other ids which have an authors in common.

Upvotes: 0

Views: 173

Answers (6)

HLGEM
HLGEM

Reputation: 96552

You said you wanted the actual ids not the count so I did this. 

   select distinct s1.id 
      from svn1 s1
      join svn2  s2
     ON s1.revno = s2.revno 
    Where s1.type = 'Bug'  
    and s2.authors IN (select authors  
                              from svn2  
                             where revno = '33')

Now for the future, you need to stop using implicit joins, they are a very poor practice. They are hard to maintain properly, subject to bugs that explicit joins are not subject to and thus riskier than using explicit joins, they contribute to developers not understanding joins well enough to properly query and they create difficulties when you need to use a left join instead of an inner join and they are 18 years outdated.

Upvotes: 0

Hogan
Hogan

Reputation: 70523

In general I also want a query to answer give an id find other ids which have an authors in common.

select distinct svn1.id 
from svn2
join svn2 link on svn2.author = link.author
join svn1 on link.revno = svn1.revno
where svn2.revno = '33'

old below

Does this work?

select count(distinct id) 
from svn1
inner join svn2 on svn1.revno = svn2.revno and svn1.authors = svn2.authors
where sv1.type = 'bug' and sv2.revno = '33'

Upvotes: 1

Spudley
Spudley

Reputation: 168685

select count(distinct id)
  from svn1 s1
  inner join svn2 s2 on s1.revno = s2.revno
  inner join svn2 s2b on s2.authors=sb2.authors and s2b.revno='33'
where
  and svn1.type = 'Bug'

I think that's equivalent to what your existing query does. It may give you a speed increase; but I can't tell without seeing the DB.

More important than refactoring the query though, do your DB tables have an indexes on the revno field? If not, add one - that'll have a far bigger effect than any tinkering with the query.

Upvotes: 0

Donald
Donald

Reputation: 1738

I'm not 100% clear on what you're trying to do, but here is my attempt:

select count(distinct id)
from svn1
inner join svn2 on svn1.revno = svn2.revno
where svn1.type = 'Bug'
and svn2.revno = '33'

Upvotes: -1

Mike Jones
Mike Jones

Reputation: 1006

You need something like this:

select count(distinct id)
from svn1 inner join svn2 on svn1.revno = svn2.revno
      inner join svn2 second on svn2.authors = second.authors
where svn1.type='Bug' and and second.revno='33'

Upvotes: 2

Erdemus
Erdemus

Reputation: 2788

select count(distinct svn1.id) 
 from svn1
 inner join svn2 on svn1.revno = svn2.revno 
where
   svn1.type='Bug' 
   and svn2.authors IN (select authors 
                          from svn2 
                         where revno='33')

Does revno and authors have indexes?

Upvotes: 2

Related Questions