SQL query for the following output?

Table test with columns compname version and table Bugs with columns bugid compname

Data for test:

A 1.2

B 1.5

C 1.6

B 1.3

C 1.5

A 1.6

B 1.6

Data for Bugs:

1 A

1 C

2 A

2 B

3 A

3 B

3 C

query is:

Output the compname where version=1.6 and affected by bugid=1 along with the fir开发者_如何学Gost(min) version in which the component appeared

Output:

A 1.2

C 1.5

I am using this query, but can this be made faster:

select compname,min(version) from test where compname IN (select compname from test where version='1.6' and compname IN (select compname from Bugs where bugid=1)) group by compname

Joins are faster, and you'll need an extra self-join to get the min version.

SELECT t.compname, min(t2.version)
FROM test t
INNER JOIN Bugs b
  ON t.compname = b.compname
INNER JOIN test t2
  ON t.compname = t2.compname
WHERE bugid = 1 AND t.version='1.6'
GROUP BY t.compname

(In my test it gave the same results you listed)

You can use INNER JOIN:

SELECT test.compname, min(test.version) 
FROM test INNER JOIN Bugs
ON test.compname = Bugs.compname
WHERE test.version = '1.6' AND Bugs.bugid =1
GROUP BY test.compname