Geek Answers Handbook

Best way to write this MySQL query?

I have a MySQL query

SELECT *, (SELECT COUNT(*) FROM B WHERE B.AID = A.ID) AS Sum1, (SELECT COUNT(*) FROM C WHERE C.AID = A.ID) AS Sum2 FROM A

What are the possible alternatives using joins or so?

+4
source share
3 answers

This will work if tables B and C have unique identifiers. In my example, the fields are called identifiers.

 SELECT A.*, COUNT(DISTINCT B.ID) AS Sum1, COUNT(DISTINCT C.ID) AS Sum2 FROM A LEFT JOIN B ON b.AID = A.ID LEFT JOIN C ON C.AID = A.ID GROUP BY A.ID
+2
source 
 SELECT A.*, IFNULL(t1.sum, 0), IFNULL(t2.sum, 0) FROM A LEFT JOIN (SELECT AID, COUNT(AID) sum FROM B GROUP BY AID) t1 ON t1.AID = A.ID LEFT JOIN (SELECT AID, COUNT(AID) sum FROM C GROUP BY AID) t2 ON t2.AID = A.ID
+2
source

Not sure if this is what you are looking for, here is how it could be achieved with join : BUT with assumptions ... that your tables look like a sample that I used as a demo. If this is not the case, please share a tabular chart and data samples with us with the expected results ...

http://sqlfiddle.com/#!2/1e65c/2

 SELECT A.ID, A.NAME, CASE WHEN B.AID = A.ID THEN COUNT(*) END AS SUM1, CASE WHEN B.AID = A.ID THEN COUNT(*) END AS SUM1 FROM A INNER JOIN B ON A.ID = B.AID INNER JOIN C ON B.AID = C.AID GROUP BY A.ID, A.NAME ; SELECT A.ID, A.NAME, COUNT(B.AID) AS SUM1, COUNT(C.AID) AS SUM1 FROM A INNER JOIN B ON A.ID = B.AID INNER JOIN C ON B.AID = C.AID GROUP BY A.ID, A.NAME ; | ID | NAME | SUM1 | -------------------- | 1 | John | 2 | | 2 | Tim | 4 | | 3 | Jack | 2 |
0
source

More articles:


All Articles