MySQL - JOIN only if a row exists from the left table

Question

Here is MySQL:

SELECT  a.id,
        a.name,
        a.n,
        a.r,
        a.pot,
        a.ticket_price,
        a.starting_tickets,
        a.started,
        a.end,
        COUNT(b.id) tickets_bought 
FROM current_lotteries a
   JOIN lottery_tickets b ON b.lid=a.id
WHERE a.cid=1 
ORDER BY started DESC LIMIT 1    

In the search, if there is no row from a but there are rows in b (i.e COUNT(b.id) is not NULL) then this query returns a row with NULL values for a fields and whatever the value of COUNT(b.id) as tickets_bought. How do I modify this query so it does not return a row (num_rows = 0) if there is no result in table a?

A Snap.

Answer 1

Absent a GROUP BY clause, MySQL (which permits this where it would be an error in other RDBMS) is applying the aggregate group over all rows in b when it should be grouping them. Add GROUP BY a.id

SELECT  a.id,
        a.name,
        a.n,
        a.r,
        a.pot,
        a.ticket_price,
        a.starting_tickets,
        a.started,
        a.end,
        COUNT(b.id) tickets_bought 
FROM current_lotteries a
   JOIN lottery_tickets b ON b.lid=a.id
WHERE a.cid=1 
GROUP BY a.id
ORDER BY started DESC LIMIT 1    

The above will work in MySQL but not elsewhere. A more portable version uses a correlated subquery:

SELECT  a.id,
        a.name,
        a.n,
        a.r,
        a.pot,
        a.ticket_price,
        a.starting_tickets,
        a.started,
        a.end,
        b.tickets_bought
FROM current_lotteries a
        /* More portable to join against a subquery which returns the count per group */
        JOIN (
            SELECT b.lid, COUNT(*) AS tickets_bought 
            FROM lottery_tickets 
            GROUP BY lid
        ) b ON a.id = b.lid
WHERE a.cid = 1
ORDER BY started DESC LIMIT 1