CODEWITHSUNDEEP
mysql
pyy
pyy

Reputation: 953

MySQL left join not counting correctly

I've currently got two tables, one contains budget information and the other is for each row in the budgets. I'm trying to select the information from the budget table, and also get the amount of rows in each budget. My current SQL is:

SELECT actual.ActualID, DateReceived, DateStart, DateEnd, ActualTotal, count(SchActualID) as NumberOfSch 
FROM actual 
LEFT JOIN schactual on actual.ActualID = schactual.ActualID 
where CentreID = 29 order by DateEnd DESC

I thought that it worked as intended, but it appears that it doesn't count the correct value. The schactual table has 4 rows in it at the moment, only one of which contains the ActualID 29, but if I run the above query the NumberOfSch is 3. Any ideas into what I'm doing wrong here?

Example input: centreID = 29

Or

centreID = 30

Example output: Say I run the query as above, it has one budget in the actual table, with a single schedule in the schActual table. The expected output would be:

ActualID | DateReceived | DateStart | DateEnd | ActualTotal | NumberOfSch

24 | 2014-11-02 | 2013-03-31 | 2014-03-31 | 1000 | 1

Current output is:

ActualID | DateReceived | DateStart | DateEnd | ActualTotal | NumberOfSch

24 | 2014-11-02 | 2013-03-31 | 2014-03-31 | 1000 | 2

The table structure looks like this: Actual:

SchActual:

Upvotes: 2

Views: 67

Answers (1)

Yohanes Khosiawan 许先汉
Yohanes Khosiawan 许先汉

Reputation: 2449

I think you just need to add group by to that query, and you are good to go.

Here is partial query of yours to simplify the solution:

UPDATED:

SELECT actual.actualid, count(schactualid) AS NumberOfSch 
FROM actual 
LEFT JOIN schactual on actual.actualid = schactual.actualid 
WHERE centreid = 29
GROUP BY actual.actualid

SQLFiddle: http://sqlfiddle.com/#!2/a0f3af/8

Upvotes: 1

Related Questions