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.

Solution 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

Solution 2

Try this:

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 
RIGHT 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;

Comments

[Keir Simmons]

No, tickets_bought is an alias.

[John Dvorak]

"this query returns a row with NULL values for a fields" I beg to differ, unless a can contain NULL values itself.

[Michael Berkowski]

That is really the query you are using? The behavior you are describing is not that of an INNER JOIN, but rather that of a RIGHT JOIN

[Keir Simmons]

I've checked the table structure, and under NULL each column has no. But when I run the above query it returns NULL for each of those fields.

[Michael Berkowski]

Wait I think I see it - there's no GROUP BY and MySQL is being its usual confusing self with the aggregate.

[Keir Simmons]

awesomescreenshot.com/0a5rwq982

[Keir Simmons]

@MichaelBerkowski Thank you. GROUP BY(b.lid) after the WHERE clause fixes it.

[Keir Simmons]

I grouped by b.lid and it seems to work, what's the difference?

[Michael Berkowski]

@KeirSimmons In that version, no difference since it is an inner join. The second query I posted is more appropriate though because it does not depend on a weird MySQL behavior.

[Andriy M]

@KeirSimmons: Without GROUP BY, it counts all rows returned by your query. But it is apparent from the query that you want counts per a.id. That's why you need to specify grouping by that criterion, and that affects the result(s) of COUNT().