Android SQLite LEFT JOIN - Date Filter is being ignored

Question

I created a query that takes the data from 3 database tables. In the table Movimenti I have a date field yyyy-MM-dd, with the where clause I want to get only the data of a given, instead I get the data from all the dates, where am I wrong?

String tabella_op = "SELECT m.id_operatore, " +
            "m.date, " +
            "m.ora_inizio, " +
            "m.minuti_inizio, " +
            "m.ora_fine, " +
            "m.minuti_fine, " +
            "m.id_servizio, " +
            "c._id, " +
            "c.nome, " +
            "c.cognome, " +
            "o.nome, " +
            "s.colore " +
            "FROM Movimenti m " +
            "LEFT JOIN Clienti c ON (m.id_cliente = c._id) " +
            "LEFT JOIN Servizi s ON (m.id_servizio = s._id) " +
            "LEFT JOIN Operatori o ON (o._id = m.id_operatore) " +
            "AND m.date = 2015-04-23 ORDER BY m.id_operatore ASC";

That doesn't appear to be correct syntax. Where is `WHERE`? I'm surprised it even executes. — Ted Hopp, Apr 23 '15 at 19:52

score 1 · Accepted Answer · edited May 23 '17 at 11:43

You need to move the filter on the primary LEFT Table (Movimenti) out of the JOIN condition, back into a WHERE clause:

String tabella_op = "SELECT m.id_operatore, " +
        "m.date, " +
        "m.ora_inizio, " +
        "m.minuti_inizio, " +
        "m.ora_fine, " +
        "m.minuti_fine, " +
        "m.id_servizio, " +
        "c._id, " +
        "c.nome, " +
        "c.cognome, " +
        "o.nome, " +
        "s.colore " +
        "FROM Movimenti m " +
        "LEFT JOIN Clienti c ON (m.id_cliente = c._id) " +
        "LEFT JOIN Servizi s ON (m.id_servizio = s._id) " +
        "LEFT JOIN Operatori o ON (o._id = m.id_operatore) " +
        "WHERE m.date = 2015-04-23 ORDER BY m.id_operatore ASC";

More on WHY this works like this here, but the TL;DR is that if any of the LEFT JOINs fail, then the filter on m.Date will be ignored altogether.

Android SQLite LEFT JOIN - Date Filter is being ignored

1 Answers1