CODEWITHSUNDEEP
mysqlsqljoinleft-join
jefffan24
jefffan24

Reputation: 1326

multiple left join's issue

$sth = $db->prepare("SELECT tblCompanies.*, users.Username, cargo.Name, (tblCompanies.Money + SUM(tblCTerminals.sellValue)) as assets, (COUNT(tblFinishedContracts.ID) + COUNT(tblFinishedSubContracts.ID)) as completed FROM ((((tblCompanies LEFT JOIN users ON tblCompanies.CompanyCEO = users.ID) LEFT JOIN cargo ON (tblCompanies.PreferredCargo = cargo.Cargo_ID)) LEFT JOIN tblCTerminals ON (tblCompanies.Company_ID = tblCTerminals.companyID)) LEFT JOIN tblFinishedContracts ON (tblCompanies.Company_ID = tblFinishedContracts.companyID)) LEFT JOIN tblFinishedSubContracts ON (tblCompanies.Company_ID = tblFinishedSubContracts.companyID) WHERE (users.Username LIKE :info || tblCompanies.CompanyName LIKE :info2 || CONCAT('$',FORMAT((tblCompanies.Money + SUM(tblCTerminals.sellValue)),2)) LIKE :info3 || CONCAT('$',FORMAT(tblCompanies.Money,2)) LIKE :info4 || cargo.Name LIKE :info5 || users.pLevel LIKE :info6 || CONCAT('$',FORMAT((users.Cash_In_Bank + users.Cash_In_Hand),2)) LIKE :info7)");

$sth->bindValue(':info', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
$sth->bindValue(':info2', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
$sth->bindValue(':info3', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
$sth->bindValue(':info4', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
$sth->bindValue(':info5', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
$sth->bindValue(':info6', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
$sth->bindValue(':info7', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);

Ok so now my reasoning for this mess:

I'm using the jQuery Datatables plugin and when it does a request to the server it sends the query. It expects back the results, how many results in the search query and how many total results. With this query I can return the results.

However on COUNT(tblFinishedContracts.ID) it is grabbing the table 8 times. So for example instead of returning the correct amount of 8 it is returning a count of 112.

When I do: 

SELECT COUNT(tblFinishedContracts.ID) 
FROM tblCompanies 
LEFT JOIN tblFinishedContract 
     ON (tblCompanies.Company_ID = tblFinishedContracts.ID)     
WHERE tblCompanies.Company_ID = 11

It works, it returns 8. If someone knows a better way of doing what I'm trying to accomplish or can tell me how to fix the issue, it would be greatly appreciated!

Thanks,

Jeff

EDIT: to add, no columns have duplicate names everything is different.

Upvotes: 0

Views: 162

Answers (1)

ruakh
ruakh

Reputation: 183361

There's no single silver bullet for this issue; getting information about a parent table, while also getting information from multiple child tables with one-to-many relationships, is always tricky. But there are a few techniques that can help.

Firstly, you don't actually do anything with tblFinishedContracts except get a count of records from it; so instead of writing COUNT(tblFinishedContracts.ID) and using a join, you can use a subquery: write (SELECT COUNT(1) FROM tblFinishedContracts WHERE Company_ID = tblCompanies.Company_ID). The same thing applies to tblFinishedSubContracts, so you can eliminate that join as well. (By the way, eliminating these joins should also improve your performance significantly, since you'll only need to retrieve information about contracts that belong to companies that are actually being returned.)

That takes care of most of the problem. The joins to users and cargo to get the CEO and preferred cargo shouldn't cause any problems. (Right?)

The only remaining difficulty is tblCTerminals, since SUM(tblCTerminals.sellValue) field occurs both in the field list and in the WHERE clause. There's no really compelling way to handle it. One option is to use the above-mentioned subquery approach, with two copies of the subquery. Another is to replace FROM tblCompanies with FROM (SELECT tblCompanies.*, COALESCE(SUM(tblCTerminals.sellValue), 0) AS totalCTerminalSellValue FROM tblCompanies LEFT JOIN tblCTerminals ON tblCTerminals.Company_ID = tblCompanies.Company_ID GROUP BY tblCompanies.Company_ID) AS tblCompanies, which in essence creates a temporary copy of tblCompanies that's augmented with a new field named totalCTerminalSellValue. So, elsewhere in the query, you can replace SUM(tblCTerminals.sellValue) with tblCompanies.totalCTerminalSellValue.

Upvotes: 1

Related Questions