CODEWITHSUNDEEP
sqlsqlite
Brian
Brian

Reputation: 1989

How do I add a count() for one table into a SQL query that has joins for multiple other tables using SQLite?

I have a sql statement that pulls data from multiple tables. This query gets all employees and their biographic information (employee id, first name, last name, badge image, etc.):

SELECT
e.ID as id,
e.DateTimeCreated as dateTimeCreated,
e.FullTime as fullTime,
bi.FirstName as firstName,
bi.LastName as lastName,
bi.MiddleName as middleName,
bi.DateOfBirth as dateOfBirth,
bi.PlaceOfBirth as placeOfBirth,
fr.Thumbnail as thumbnail
FROM employee e
LEFT JOIN biographicinfo bi
ON e.ID = bi.employeeId
LEFT JOIN facialraw fr
ON e.ID = fr.EnrollmentId
AND fr.CaptureAngle = 2
GROUP BY e.ID

Now, there is another table called, say, EmployeeDiscrepencies that I would like to do a count on:

select count(1) as "Discrepencies" from EmployeeDiscrepencies ed where ed.employeeId = 2;

I would like to add this count statement into my original query when I grab all employees, but I cannot figure out how to do it. I wanted to try something like this:

SELECT
e.ID as id,
e.DateTimeCreated as dateTimeCreated,
e.FullTime as fullTime,
bi.FirstName as firstName,
bi.LastName as lastName,
bi.MiddleName as middleName,
bi.DateOfBirth as dateOfBirth,
bi.PlaceOfBirth as placeOfBirth,
fr.Thumbnail as thumbnail,
ed.count(1), -- need a "where" clause here
FROM employee e
LEFT JOIN biographicinfo bi
ON e.ID = bi.employeeId
LEFT JOIN facialraw fr
ON e.ID = fr.EnrollmentId
LEFT JOIN EmployeeDiscrepencies ed
on e.ID = ed.employeeID
AND fr.CaptureAngle = 2
GROUP BY e.ID

As you can guess, it is not working

How do I go about accomplishing this?

Upvotes: 0

Views: 30

Answers (2)

forpas
forpas

Reputation: 164214

If you want only that counter from EmployeeDiscrepencies then the simplest way is to use a subquery to return it like this:

SELECT
e.ID as id,
e.DateTimeCreated as dateTimeCreated,
e.FullTime as fullTime,
bi.FirstName as firstName,
bi.LastName as lastName,
bi.MiddleName as middleName,
bi.DateOfBirth as dateOfBirth,
bi.PlaceOfBirth as placeOfBirth,
fr.Thumbnail as thumbnail,
(SELECT COUNT(*) FROM EmployeeDiscrepencies ed WHERE e.ID = ed.employeeID) as Discrepencies
FROM employee e
LEFT JOIN biographicinfo bi
ON e.ID = bi.employeeId
LEFT JOIN facialraw fr
ON e.ID = fr.EnrollmentId
AND fr.CaptureAngle = 2
GROUP BY e.ID

If your condition is for employeeId = 2 only then a cross join of your query to the subquery would be more efficient:

SELECT
e.ID as id,
e.DateTimeCreated as dateTimeCreated,
e.FullTime as fullTime,
bi.FirstName as firstName,
bi.LastName as lastName,
bi.MiddleName as middleName,
bi.DateOfBirth as dateOfBirth,
bi.PlaceOfBirth as placeOfBirth,
fr.Thumbnail as thumbnail,
ed.counter as Discrepencies
FROM employee e
CROSS JOIN (SELECT COUNT(*) count FROM EmployeeDiscrepencies ed WHERE  ed.employeeID = 2) ed
LEFT JOIN biographicinfo bi
ON e.ID = bi.employeeId
LEFT JOIN facialraw fr
ON e.ID = fr.EnrollmentId
AND fr.CaptureAngle = 2
GROUP BY e.ID

Upvotes: 1

Aleksandar Zoric
Aleksandar Zoric

Reputation: 1483

Would something like this work for you - I did it quick and did not check for error so just make sure all closing/opening brackets are in correct place.

   SELECT
   e.ID as id,
   e.DateTimeCreated as dateTimeCreated,
   e.FullTime as fullTime,
   bi.FirstName as firstName,
   bi.LastName as lastName,
   bi.MiddleName as middleName,
   bi.DateOfBirth as dateOfBirth,
   bi.PlaceOfBirth as placeOfBirth,
   fr.Thumbnail as thumbnail,
   [EmployeeDis].[Discrepencies] 
FROM
   employee e 
OUTER APPLY ( 
   select
      count(1) as [Discrepencies] 
   from
      EmployeeDiscrepencies ed 
   where
      ed.employeeId = 2;
) as [EmployeeDis] 
      LEFT JOIN
         biographicinfo bi 
         ON e.ID = bi.employeeId 
      LEFT JOIN
         facialraw fr 
         ON e.ID = fr.EnrollmentId 
         AND fr.CaptureAngle = 2 
   GROUP BY
      e.ID

Upvotes: 0

Related Questions