I want to select all the data specified from multiple tables, but it only select one data

Question

I have written a SQL query through which I want to select all the related data specified from multiple tables but when I run the query it only retrieve single data but I want to select all the data. Here is my query:

    $mysqli->query("SELECT projectDescription, location,budget,date, 
    SUM(ebudget) as expense_budget, SUM(totalAmount) as overtime_budget, 
    SUM(totalPrice) as material_budget FROM projecttable, expensestable, 
    overtimetable,dailyworkusage WHERE 
    expensestable.projectID=projecttable.projectID AND 
    overtimetable.projectID=projecttable.projectID AND 
    dailyworkusage.projectID=projecttable.projectID") or die($mysqli- 
    >error());

As GMB mentioned you likely need group-by. Also see this [question](https://stackoverflow.com/questions/28497082/mysql-aggregate-functions-without-group-by-clause) for possible explanation. You also need to include mysql tag. — Ravindra HV, Oct 12 '19 at 18:41
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Oct 12 '19 at 20:48

GMB · Accepted Answer · 2019-10-12T18:42:08.223

That's because you are missing a GROUP BY clause (in non-ancient versions of MySQL, you would have met a syntax error). Add this at the end of your query:

GROUP BY projectDescription, location, budget, date

Also, you do want to use explicit joins instead of old-school, implicit joins. Using aliases for table names is also a good practice. Here is an updated version of your query:

SELECT 
    projectDescription, 
    location,
    budget,
    date, 
    SUM(ebudget) as expense_budget, 
    SUM(totalAmount) as overtime_budget, 
    SUM(totalPrice) as material_budget 
FROM projecttable p
INNER JOIN expensestable e ON e.projectID = p.projectID  
INNER JOIN overtimetable o ON o.projectID = p.projectID 
INNER JOIN dailyworkusage d ON d.projectID = p.projectID
GROUP BY 
    projectDescription, 
    location,
    budget,
    date

Please add the relevant table prefixes to all these columns (it cannot be told without seeing your actual table structures, that you did not show).

@Xeroseven: this query returns one record for each combination of `projectDescription, location, budget, date`. If you want a different cardinality, you can change the columns in the `SELECT` and the `GROUP BY` clause accordingly. — GMB, Oct 12 '19 at 19:59

I want to select all the data specified from multiple tables, but it only select one data

1 Answers1