Lollipop Lollipop - 6 months ago 9
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 ;
Comments