Complex Joins

[ShadowMage]

Hi all,I have a potentially challenging question. Only challenging because I can't give you database structure and data, but here's the situation.I have this SQL statement:

$sql = "SELECT OrderHed.OrderNum, OrderDtl.OrderLine, OrderHed.OrderHeld, OrderHed.NeedByDate, OrderHed.SalesRepList, OrderHed.CustNum, LCASE(OrderHed.Character01) AS 'Estimator', Customer.Name AS 'CustName', (OrderDtl.UnitPrice * OrderDtl.OrderQty) AS 'Value', LCASE(OrderDtl.PricePerCode) as 'PricePerCode', OrderDtl.PartNum, OrderDtl.Number02, JobHead.CheckOff1, JobHead.CheckOff2, JobHead.CheckOff3, JobHead.CheckOff4, JobHead.CheckOff5, JobHead.UserDate4, JobHead.UserInteger1, LCASE(JobHead.UserChar1) AS 'SubDwgBy', LCASE(JobHead.UserChar2) AS 'ProdDwgBy', LCASE(JobHead.UserChar3) AS 'EngLeader', JobHead.UserDate1 AS 'RelToDrftDate', JobHead.UserDate2 AS 'ResubRequestDate', JobHead.DueDate, JobHead.Character01, JobHead.Date01, JobHead.Date02, JobHead.Date03, JobHead.SchedCode, ShipTo.State, ShipTo.TerritoryID ";$sql.="FROM OrderHed OrderHed INNER JOIN Customer Customer ON (OrderHed.Company = Customer.Company AND OrderHed.CustNum = Customer.CustNum), OrderDtl OrderDtl LEFT OUTER JOIN JobProd JobProd ON (OrderDtl.Company = JobProd.Company AND OrderDtl.OrderNum = JobProd.OrderNum AND OrderDtl.OrderLine = JobProd.OrderLine), JobHead JobHead, ShipTo ShipTo ";$sql.="WHERE ( (OrderHed.Company = OrderDtl.Company AND OrderHed.OrderNum = OrderDtl.OrderNum AND ShipTo.Company = OrderHed.Company AND ShipTo.CustNum = OrderHed.CustNum AND ShipTo.ShipToNum = OrderHed.ShipToNum ";$sql.="AND JobProd.Company = JobHead.Company AND JobProd.JobNum = JobHead.JobNum) AND ( (OrderHed.OpenOrder <> 0) "; // AND (OrderDtl.OpenLine <> 0) AND (OrderDtl.VoidLine = 0)if ($postEstimator != 'allestimators') {	$sql.="AND (LCASE(OrderHed.Character01) = '".$postEstimator."') ";}if ($postSalesRep != 'allreps') {	$sql.="AND (OrderHed.SalesRepList LIKE '%".$postSalesRep."%') ";}if ($arrCustomer[0] != 'allcustomers') {	if (($arrCustomer[0] != 'allarch') && ($arrCustomer[0] != 'allgc') && ($arrCustomer[0] != 'allrep') && ($arrCustomer[0] != 'allother')) {		$sql.="AND (Customer.CustNum = '".$arrCustomer[0]."') ";	} else {		switch ($arrCustomer[0]) {			case 'allarch':				$sql.="AND (LCASE(Customer.GroupCode ) = 'arch') ";				break;			case 'allgc':				$sql.="AND (LCASE(Customer.GroupCode ) = 'gc') ";				break;			case 'allrep':				$sql.="AND (LCASE(Customer.GroupCode ) = 'rep') ";				break;			default:				$sql.="AND ( (LCASE(Customer.GroupCode ) <> 'arch') AND (LCASE(Customer.GroupCode ) <> 'gc') AND (LCASE(Customer.GroupCode ) <> 'rep') ) ";				break;		}	}}$sql.=") ) ORDER BY OrderHed.OrderNum ASC, OrderDtl.OrderLine ASC";

I have records in the OrderHed, OrderDtl tables that are not in the JobProd, JobHead tables that I need to pull from the DB to process. Trouble is I can't figure out how to write the joins so that it pulls those records. Right now it only pulls records that are in all of the tables.Is it possible for someone to help me solve this issue?Thanks in advance for any help/advice you can give.

[justsomeguy]

A left join or right join will select all of the rows from one table and only the matching rows from the other, and fill in null values where there aren't matching records.

[ShadowMage]

I have a left outer join:OrderDtl OrderDtl LEFT OUTER JOIN JobProd JobProdIt doesn't seem to work. The records from the JobProd table still don't show up in the recordset.I suspect it has something to do with using the WHERE clause to join the tables, but I can't seem to get my inner joins and outer joins to work.I've tried several ways of writing the joins but the only way I don't get a syntax error is by using the where clause.How do I write the SQL so that I can join the OrderHed to the OrderDtl and the Customer, then outer join the OrderDtl to the JobProd?I think that's how it needs to be done I just can't seem to get it right.

[justsomeguy]

I have a left outer join:OrderDtl OrderDtl LEFT OUTER JOIN JobProd JobProdIt doesn't seem to work. The records from the JobProd table still don't show up in the recordset.

That's because the "left" table is the OrderDtl table, not the JobProd table. So it's taking all of the rows from the left table and matching whatever it can from the right table. A right join would take all of the rows from JobProd and only the matching rows from OrderDtl. A full join should take all records from both tables and try to match them.

I suspect it has something to do with using the WHERE clause to join the tables

When in doubt, remove the WHERE clause. All it does is reduce (filter) the result set.

[ShadowMage]

That's because the "left" table is the OrderDtl table, not the JobProd table. So it's taking all of the rows from the left table and matching whatever it can from the right table. A right join would take all of the rows from JobProd and only the matching rows from OrderDtl. A full join should take all records from both tables and try to match them.

Oops, my bad. I meant to say the records in the OrderDtl table aren't showing up. The records exist in the OrderDtl but NOT in the JobProd table.Anyway I'll try removing the WHERE clause to see if anything happens.

[ShadowMage]

Removed the WHERE clause but that left the recordset blank.I tried adding it back piece by piece but it never pulled any records until everything was back to the way it was.

[justsomeguy]

One issue may be that you're using implicit inner joins for several tables. When you separate tables with commas it just uses an inner join and returns all possible combinations without matching up specific fields. That's not very efficient, it's always better to specify the type of join you want to use and the columns to join on instead of having it join everything and then using WHERE conditions to filter the results.I'm not sure why removing a where clause would reduce the number of results though, that's not how a where works. The way it executes the query is it creates a temporary table with the results that it finds, and once it has all of the results then it uses the where clauses to remove records that don't match. So where clauses don't add any records, they only remove things that don't match.

[ShadowMage]

Got it! Here's how my joins look now:

$sql.="FROM OrderHed OrderHed INNER JOIN Customer Customer ON (OrderHed.Company = Customer.Company AND OrderHed.CustNum = Customer.CustNum) INNER JOIN OrderDtl OrderDtl ON (OrderHed.Company = OrderDtl.Company AND OrderHed.OrderNum = OrderDtl.OrderNum) LEFT OUTER JOIN JobProd JobProd ON (OrderDtl.Company = JobProd.Company AND OrderDtl.OrderNum = JobProd.OrderNum AND OrderDtl.OrderLine = JobProd.OrderLine) LEFT OUTER JOIN JobHead JobHead ON (JobProd.Company = JobHead.Company AND JobProd.JobNum = JobHead.JobNum) INNER JOIN ShipTo ShipTo ON (ShipTo.Company = OrderHed.Company AND ShipTo.CustNum = OrderHed.CustNum AND ShipTo.ShipToNum = OrderHed.ShipToNum) ";

Thanks, justsomeguy, for helping me with this.