I have a PHP page like this: ID Name Hours Flown 1 Joao 7 2 Andre 10 3 Tiago 15 And I want that "Hours Flown" column show a value from one column from a SQL table (users) with a sum of ...

Sum values from a SQL table to a value from other SQL table

I have a PHP page like this:

ID	Name	Hours Flown
1	Joao	7
2	Andre	10
3	Tiago	15

And I want that “Hours Flown” column show a value from one column from a SQL table (users) with a sum of another SQL table (flights) where ID matches the “users” table.

I have a while($row = $result_list_pilots->fetch_assoc()) to show the table if that helps…

SQL query for now is a bit simple “SELECT * FROM users ORDER BY name;“. Already tried some JOIN examples but without success as the imported_hours gets duplicated as result from flights table is being found.

Tables: [Tables]

How to do this?

Answer

I think here is what you want :

SELECT 
u.ID
, u.Name
, SUM(ISNULL(f.HoursFlown,0)) + MAX(Imported_hours) 'Hours Flown'
FROM Users u 
LEFT JOIN Flights f 
ON u.ID = f.User_id -- or whatever the fk is 
GROUP BY u.ID, u.Name

JavaScript
​x
 
SELECT u.ID, u.Name, SUM(ISNULL(f.HoursFlown,0)) + MAX(Imported_hours) 'Hours Flown'FROM Users u LEFT JOIN Flights f ON u.ID = f.User_id -- or whatever the fk is GROUP BY u.ID, u.Name​

Advertisement

Answer