Trying to display the pilots with zero Flight Miles-CodePudding

the following query does what I want MOSTLY

SELECT DISTINCT CONCAT(TP.intPilotID, ' ', TP.strFirstName, ' ', TP.strLastName, ', ', SUM(TF.intMilesFlown)) AS MILES
                        FROM TFlights AS TF
                        JOIN TPilotFlights as TPF
                        ON TF.intFlightID=TPF.intFlightID
                        JOIN TPilots AS TP
                        ON TPF.intPilotID = TP.intPilotID
                        GROUP BY TP.intPilotID, TP.strFirstName, TP.strLastName

how can I get it to show the one guy who hasn't flown yet (and display his miles as zero)

I have tried rearranging the order of the joins which only messed up the mileage totals

CodePudding user response：

as mentioned can be accomplished with a left outer join or a union all on pilots that do not have flights here is the union all scenario

   SELECT 
      DISTINCT CONCAT(
        TP.intPilotID, 
        ' ', 
        TP.strFirstName, 
        ' ', 
        TP.strLastName, 
        ', ' as name, 
        SUM(TF.intMilesFlown)
      ) AS MILES 
    FROM 
      TFlights AS TF 
      JOIN TPilotFlights as TPF ON TF.intFlightID = TPF.intFlightID 
      JOIN TPilots AS TP ON TPF.intPilotID = TP.intPilotID 
    GROUP BY 
      TP.intPilotID, 
      TP.strFirstName, 
      TP.strLastName 
    union all 
    select 
      CONCAT(
        TP.intPilotID, 
        ' ', 
        TP.strFirstName, 
        ' ', 
        TP.strLastName, 
        ', ' as name, 
        0 as miles 
        from 
          TPilots AS TP 
        where 
          not exists (
            select 
              1 
            from 
              TPilotFlights as TPF 
            where 
              TPF.intPilotID = TP.intPilotID
          )