Join two SQL queries together but using a variable from the frist query to perform the second-CodePudding

I'm trying to perform an SQL query in MySQL, and then 'right' join the column of the second query.

However, in order to perform the second query I need a variable from the result of the first (the employee number).

This example displays the employees in a particular team, but also ads a column that shows how many times that employee in that row has failed to turn up for a shift.

the employee log table only holds the employee id number and whether they showed up or not to their shift.

SELECT employee.id_number, employee.name, employee.surname 
FROM employee 
WHERE employee.team_name = "alpha";

Select count(employee_log.attendance_type) 
FROM employee_log 
WHERE employee_log.attendance_type = "No-Show"; AND (match with the returned employee.id_number from the 1st query....then join this column onto the right of the query above....right inner join I suppose?)

I was not sure what I was looking for when posting this, I assumed maybe an inner join operation or sub query? it's the first complicated query i've had to try and develop.

If anyone could lend me a hand I'd be very appreciative.

Thanks

CodePudding user response：

Below query should help If I get it correct.

Select count(employee_log.attendance_type) 
FROM employee_log log
JOIN (
    SELECT employee.id_number, employee.name, employee.surname 
    FROM employee 
    WHERE employee.team_name = "alpha";
) emp ON emp.id_number = log.id_number
WHERE employee_log.attendance_type = "No-Show"

CodePudding user response：

What it looks like you're after is a join. There are multiple join types:

INNER JOIN (Both tables have to have the data)
LEFT JOIN (all records from the LEFT table and those that match on right)
RIGHT JOIN (All records from the RIGHT table and those that match from left)
FULL OUTER ( All records from both tables aligning where they match)
CROSS JOIN (All records from both tables related to each other (5 records in 1 table, 10 in another produces (5*10) 50 records)
OUTER APPLY (A table value function which will return the results of a sub query for each record in teh outer. For example, maybe I want the top 10 newest log entries for each employee we could select top 10 and correlate the employeeIDs; but you don't need this here)
UNION, MINUS, EXCEPT, UNION ALL, INTERSECT... are all SET operators and not technically joins. They evaluate data sets as a whole against other data sets. UNION combines both sets and eliminates duplicates, EXCEPT/MINUS removes data from first set found in second, and intersect shows where they exist in both sets. UNION ALL combines both and keeps Duplicates. The set operators do not seem germane to your question.

Assumption

employee_log has a field called id_number which is a foreign key to employee.id_number
we use coalesce incase any employee in alpha has NO entries in the log table. In this case we want 0 to be displayed instead of NULL

Notes:

aliased tables for improve readability (E) for employee (EL) for Employee Log
used LEFT join (include all records in Employee Table, and only those that match in the employee Log.
applied filter on attendance_type on the join itself. If we put this in the where clause it would negate the left join making it behave like an inner join.
Left join, right join it's about the order of the tables. So we want to show all alpha team employees and the # of times they. didn't show up.

Process:

System retrieves all employees of 'alpha' team
System gets all logs files of 'no-show' associated to an employee of alpha
system counts those entries of no-show grouping by the ID, first, last names
system renders output

SELECT E.id_number, E.name, E.surname, coalesce(count(EL.attendance_type),0) as NoShows
FROM employee E
LEFT JOIN employee_log EL
  on E.id_number = EL.id_number
 AND EL.attendance_type = 'No-Show'
WHERE E.team_name = 'alpha'
GROUP BY E.id_number, E.name, E.surname