CODEWITHSUNDEEP
mysql
DDay
DDay

Reputation: 100

solve mysql query

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

Upvotes: 0

Views: 57

Answers (2)

Vamshi .goli
Vamshi .goli

Reputation: 520

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

Upvotes: 1

Manoj Sharma
Manoj Sharma

Reputation: 616

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

Upvotes: 1

Related Questions