Issue with SQL join with 4 tables, where one is a parent Table

LEFT JOIN customers ON (traveldocuments.ID=customers.ID)

I posted this in your other question before I saw this one so I'll post it here as well:

Left joins will pull all rows from traveldocuments even if there are no matches in the other tables.  When there isn't a match, it will return nulls for those fields.

So, a null in the customer name means there isn't a customer.id that matches a traveldocuments.id value.

If you think it should return data, please provide sample data for all three tables and expected results from that data.  Then I can set up a test case.

Since you are new, thought I would offer a suggestion to save you some typing.

You can alias the table names and use the alias on the columns:

SELECT 
	t.TDNumber, 
	t.TDMoveStart, 
	t.TD_ODR_O_Address, 
	t.TD_ODR_D_Address, 
	a.ApprovalJurisdictionFee, 
	a.ApprovalPermitNowFee, 
	co.CommoditySteerUnitNumber, 
	co.CommodityName, 
	cu.CustomerName 
FROM traveldocuments as t 
LEFT JOIN approvals as a ON (t.Id=a.TDId) 
LEFT JOIN commodities as co ON (t.Id=co.TDId) 
LEFT JOIN customers as cu ON (t.customerID=cu.Id); 

Select allOpen in new window

Off the top of my head: the ROW_NUMBER() function:

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number

You need something in commodities that signifies "first".  Something to order by.

Something like this:

SELECT 
	t.TDNumber, 
	t.TDMoveStart, 
	cu.CustomerName, 
	co.CommoditySteerUnitNumber, 
	co.CommodityName, 
	t.TD_ODR_O_Address, 
	t.TD_ODR_D_Address, 
	a.ApprovalPermitNowFee, 
	a.ApprovalJurisdictionFee, 
	a.ApprovalTotalFee 
FROM traveldocuments t 
	LEFT JOIN approvals a ON (traveldocuments.Id=approvals.TDId) 
	LEFT JOIN ( 
			select TDId, 
				CommoditySteerUnitNumber, 
				CommodityName 
			from ( 
				select TDId, 
					CommoditySteerUnitNumber, 
					CommodityName, 
					row_number() over(partition by TDId order by CommodityName) rn 
				from commodities 
			) 
			where rn=1 
		) co 
			ON (t.Id=co.TDId) 
	LEFT JOIN customers cu ON (t.customerID=cu.Id) 
ORDER BY TDNumber DESC; 

Select allOpen in new window

If you want copy/paste code, we need the test case.  Short of that, try to understand what is provided.

The error should point you right to it.

I aliased the table traveldocuments to "t".  After that you need to use "t." everywhere.

I forgot to use the aliases I creates in one of the ON clauses.

Change:

LEFT JOIN approvals a ON (traveldocuments.Id=approvals.TDId)  

To:

LEFT JOIN approvals a ON (t.Id=a.TDId)  

>> Didn't know you could add so much Syntax to the Join....amazing!

Same basic concept.  I just used an inline view to replace the actual table.

As long as you think of the two as:  something selected from to return columns and rows, they are the same...

There are other ways, like Common Table Expressions (CTE):

https://mariadb.com/kb/en/with/

The inline view was just quicker for me to copy/paste together.

The CTE versions should go something like:

with 
top_commodities as (   
	select TDId,   
		CommoditySteerUnitNumber,   
		CommodityName   
	from (   
		select TDId,   
			CommoditySteerUnitNumber,   
			CommodityName,   
			row_number() over(partition by TDId order by CommodityName) rn   
		from commodities   
	) x  
	where rn=1   
)  
SELECT  
	t.TDNumber,   
	t.TDMoveStart,   
	cu.CustomerName,   
	co.CommoditySteerUnitNumber,   
	co.CommodityName,   
	t.TD_ODR_O_Address,   
	t.TD_ODR_D_Address,   
	a.ApprovalPermitNowFee,   
	a.ApprovalJurisdictionFee,   
	a.ApprovalTotalFee   
FROM traveldocuments t   
	LEFT JOIN approvals a ON (traveldocuments.Id=approvals.TDId)   
	LEFT JOIN top_commodities co   
			ON (t.Id=co.TDId)   
	LEFT JOIN customers cu ON (t.customerID=cu.Id)   
ORDER BY TDNumber DESC;

Select allOpen in new window

You might even get away with:

with 
top_commodities as (   
		select TDId,   
			CommoditySteerUnitNumber,   
			CommodityName,   
			row_number() over(partition by TDId order by CommodityName) rn   
		from commodities   
)  
SELECT  
	t.TDNumber,   
	t.TDMoveStart,   
	cu.CustomerName,   
	co.CommoditySteerUnitNumber,   
	co.CommodityName,   
	t.TD_ODR_O_Address,   
	t.TD_ODR_D_Address,   
	a.ApprovalPermitNowFee,   
	a.ApprovalJurisdictionFee,   
	a.ApprovalTotalFee   
FROM traveldocuments t   
	LEFT JOIN approvals a ON (traveldocuments.Id=approvals.TDId)   
	LEFT JOIN top_commodities co   
			ON (t.Id=co.TDId and co.rn=1)   
	LEFT JOIN customers cu ON (t.customerID=cu.Id)   
ORDER BY TDNumber DESC;

Select allOpen in new window

The LEFT JOIN should take care of that.  You won't have any "t.Id=co.TDId" so the "rn=1" shouldn't matter.