How to fix SQL query with Left Join and subquery?

Question

I have SQL query with LEFT JOIN:

SELECT COUNT(stn.stocksId) AS count_stocks
FROM MedicalFacilities AS a
LEFT JOIN stocks stn ON 
(stn.stocksIdMF = ( SELECT b.MedicalFacilitiesIdUser 
                        FROM medicalfacilities AS b 
                        WHERE b.MedicalFacilitiesIdUser = a.MedicalFacilitiesIdUser 
                        ORDER BY stn.stocksId DESC LIMIT 1) 

AND stn.stocksEndDate >= UNIX_TIMESTAMP() AND stn.stocksStartDate <= UNIX_TIMESTAMP())

These query I want to select one row from table stocks by conditions and with field equal value a.MedicalFacilitiesIdUser.

I get always count_stocks = 0 in result. But I need to get 1

Are you sure about `stn.stocksEndDate >= UNIX_TIMESTAMP() AND stn.stocksEndDate <= UNIX_TIMESTAMP()`? Seems it should be replace'able by just `stn.stocksEndDate = UNIX_TIMESTAMP()`, but I doubt that's what you mean to do. — Joachim Isaksson, Jul 26 '15 at 10:24
@JoachimIsaksson: good point, or maybe `UNIX_TIMESTAMP() between stn.stocksStartDate and stn.StocksEndDate` — Andomar, Jul 26 '15 at 10:28
Yes, one problem was in condition with `UNIX_TIMESTAMP()`. Updated question — Babaev, Jul 26 '15 at 10:47
I think that is wrong sub-query, because I need select one row from `stocks` — Babaev, Jul 26 '15 at 10:51
Now the query has start and end time mixed. Start time should be smaller than the current timestamp, not larger. — Andomar, Jul 26 '15 at 10:52

score 2 · Answer 1 · answered Jul 26 '15 at 10:24

2

The count(...) aggregate doesn't count null, so its argument matters:

COUNT(stn.stocksId)

Since stn is your right hand table, this will not count anything if the left join misses. You could use:

COUNT(*)

which counts every row, even if all its columns are null. Or a column from the left hand table (a) that is never null:

COUNT(a.ID)

answered Jul 26 '15 at 10:24

Andomar

232,371
49
380
404

I can not use `count(*)` because I have some others `LEFT JOIN`. – Babaev Jul 26 '15 at 10:48

score 1 · Answer 2 · answered Jul 26 '15 at 12:27

Your subquery in the on looks very strange to me:

on stn.stocksIdMF = ( SELECT b.MedicalFacilitiesIdUser 
                      FROM medicalfacilities AS b 
                      WHERE b.MedicalFacilitiesIdUser = a.MedicalFacilitiesIdUser 
                      ORDER BY stn.stocksId DESC LIMIT 1)

This is comparing MedicalFacilitiesIdUser to stocksIdMF. Admittedly, you have no sample data or data layouts, but the naming of the columns suggests that these are not the same thing. Perhaps you intend:

on stn.stocksIdMF = ( SELECT b.stocksId 
-----------------------------^
                      FROM medicalfacilities AS b 
                      WHERE b.MedicalFacilitiesIdUser = a.MedicalFacilitiesIdUser 
                      ORDER BY b.stocksId DESC
                      LIMIT 1)

Also, ordering by stn.stocksid wouldn't do anything useful, because that would be coming from outside the subquery.

score 0 · Accepted Answer · answered Jul 26 '15 at 17:06

Your subquery seems redundant and main query is hard to read as much of the join statements could be placed in where clause. Additionally, original query might have a performance issue.

Recall WHERE is an implicit join and JOIN is an explicit join. Query optimizers make no distinction between the two if they use same expressions but readability and maintainability is another thing to acknowledge.

Consider the revised version (notice I added a GROUP BY):

SELECT COUNT(stn.stocksId) AS count_stocks 
FROM MedicalFacilities AS a 
LEFT JOIN stocks stn ON stn.stocksIdMF = a.MedicalFacilitiesIdUser
WHERE stn.stocksEndDate >= UNIX_TIMESTAMP() 
  AND stn.stocksStartDate <= UNIX_TIMESTAMP()
GROUP BY stn.stocksId
ORDER BY stn.stocksId DESC
LIMIT 1

How to fix SQL query with Left Join and subquery?

3 Answers3