0

Today I have been asked a question by an interviewer that stated we have three tables named as table A, B, and C.

Those tables are like this

       A                        B                             C
------------------  --------------------------   ----------------------------
| ID | ProjectID |  | ID | LocationID |  aID |   | ID | points | LocationID |
------------------  --------------------------   ----------------------------
| 1  |    15     |  | 1  |    131     |   1  |   | 1  | 123333 |     131    |
| 2  |    15     |  | 2  |    132     |   1  |   | 2  | 123223 |     132    |
| 3  |    15     |  | 3  |    133     |   1  |   | 3  | 522    |     211    |
| 4  |    12     |  | 4  |    134     |   2  |   | 4  | 25     |     136    |
------------------  | 5  |    136     |   2  |   | 5  | 25     |     133    |
                    | 6  |    137     |   3  |   | 6  | 25     |     134    |
                    | 7  |    138     |   1  |   | 7  | 25     |     135    |
                    --------------------------   ----------------------------

now he told me to write a query that sums the points of those locations whose project is 15.

First i wrote the query to get ID's from table A like this

SELECT ID from A where projectID  = 15

then i pass this result in table b query just like this

SELECT LocationID FROM B WHERE aID IN ( SELECT ID from A where projectID  = 15 )

Then i calculate the sum of these locations just like this

SELECT SUM(points) from C where LocationID IN(SELECT LocationID FROM B WHERE aID IN ( SELECT ID from A where projectID = 15))

My Result is fine and query is correct. But he rejected my answer by saying that this nested IN Clause will slow down the whole process as when we have thousands of records. Then he gave me another chance to review my answer but i couldn't figure it out.

Is there anyway to optimize this or is there some other way to do the same. Any help? Thanks

DDay
  • 100
  • 1
  • 1
  • 9

2 Answers2

1

Try this it may solve your problem.

Select SUM(C.points) FROM C JOIN B ON C.LocationID = B.LocationID JOIN A ON B.aID = A.ID where A.ProjectID = 15 GROUPBY A.ProjectID
Manoj Sharma
  • 596
  • 1
  • 6
  • 23
  • i tried this but this goes into infinite loop as it keep saying processing, processing and processing. – DDay Jan 14 '15 at 10:42
  • Its not will give you 246631 as output in 0.0010 second on 0.0000 as i have checked in my system with the data you have provided. Please check at your end. – Manoj Sharma Jan 14 '15 at 11:04
  • Yeah it was an error on my End. Thanks it worked for me. and also vamshi's answer. i voted up for both the answers but not accepted any as i can't. Thanks Again – DDay Jan 14 '15 at 11:37
1

Try with this....i hope it will work

 select sum(c.points) as sum_points 
 from A a,B b,C c where 
 a.ID=b.aID and 
 b.LocationID=c.LocationID
 and a.projectID=15
Vamshi .goli
  • 522
  • 4
  • 13