Lollipop Lollipop - 1 year ago 36
SQL Question

How to get the data in SQLite with "LEFT ON"

SELECT CaseFolder.CaseId, CaseFolder.CaseCoverImageFileName,
CaseFolder.CaseName, count(Event.CaseId), CaseFolder.CaseColor,
CaseFolder.IsMedicalCase, CaseFolder.StartDate, CaseFolder.EndDate
FROM CaseFolder
LEFT JOIN Event ON CaseFolder.CaseId = Event.CaseId
WHERE Event.PersonId = 1 AND Event.IsDeleted = 0 AND CaseFolder.IsDeleted = 0
GROUP BY CaseFolder.CaseId


I select
Count
by table
Event
and another by table
CaseFolder
.The
primary key
is
CaseId
,But I need to select data that is included in a table but not in another table,when must add "where" with "LEFT ON".If I delete the sql code
WHERE Event.PersonId = 1 AND Event.IsDeleted = 0 AND CaseFolder.IsDeleted = 0
the result is right.Because the
WHERE
contains conditions that can't suit in table
CaseFolder
.what should i do?

Answer

Conditions on the second table in a left join should go in the on clause:

SELECT CaseFolder.CaseId, CaseFolder.CaseCoverImageFileName,
       CaseFolder.CaseName, count(Event.CaseId), CaseFolder.CaseColor,
       CaseFolder.IsMedicalCase, CaseFolder.StartDate, CaseFolder.EndDate
FROM CaseFolder LEFT JOIN
     Event
     ON CaseFolder.CaseId = Event.CaseId AND
        Event.PersonId = 1 AND Event.IsDeleted = 0 
WHERE CaseFolder.IsDeleted = 0;
GROUP BY CaseFolder.CaseId ;

Otherwise, the LEFT JOIN turns into an INNER JOIN, because the condition fails with a NULL value. Note, your query is easier to write and to read with table aliases:

SELECT cf.CaseId, cf.CaseCoverImageFileName,
       cf.CaseName, count(e.CaseId), cf.CaseColor,
       cf.IsMedicalCase, cf.StartDate, cf.EndDate
FROM CaseFolder cf LEFT JOIN
     Event e
     ON cf.CaseId = e.CaseId AND
        e.PersonId = 1 AND e.IsDeleted = 0 
WHERE cf.IsDeleted = 0;
GROUP BY cf.CaseId ;