We help IT Professionals succeed at work.

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

G F
G F asked
on
20 Comments
1 solution
High Priority
56 Views
Last Modified: 2020-02-28
I'm using SQL joins from three child tables, to form one table, and that works fine.
However, I would like to pull the name of a customer from a table that is a parent, in the same SQL statement.

Here is the code to my working Three Table Join:

SELECT

traveldocuments.TDNumber,
traveldocuments.TDMoveStart,
traveldocuments.TD_ODR_O_Address,
traveldocuments.TD_ODR_D_Address,
approvals.ApprovalJurisdictionFee,
approvals.ApprovalPermitNowFee,
commodities.CommoditySteerUnitNumber,
commodities.CommodityName

FROM traveldocuments
LEFT JOIN approvals ON (traveldocuments.Id=approvals.TDId)
LEFT JOIN commodities ON (traveldocuments.Id=commodities.TDId);



I tried adding the field: customers.CustomerName, and another Join:  LEFT JOIN customers ON (traveldocuments.ID=customers.ID);

But all I get are NULL values in the Joined Column, instead of the Customer Names.

So I'm not sure how to get the Name of the Customer, corresponding to the TDId.  With the other tables, approvals and commodities, they are child tables, but the now the Customers is a parent Table to traveldocuments.

Any help would be appreciated.

Thank you!
G
Comment
Watch Question

View Solution Only

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

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

you need to make sure you're joining the correct fields in the tables.

for example, do you have a field called as "CustomerID" in table: traveldocuments , instead of "ID" ?
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
As stated, you are probably joining on the incorrect column.

Also, the join to CUTOMER, shouldn't be a LEFT JOIN.  I don't see how  you could possibly have a travel document without a customer.
slightwv (䄆 Netminder)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

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.


awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
While a full set of sample table data and expected results would be the best help, could you at least provide the link that needs to exist between the customer table and one of the others.
G FGeneral Manager

Author

Commented:
Thanks to everyone for commenting...

Ryan,

Yes, I do have a CustomerID field in my travel documents table, here is a snapshot of the travel documents:


Travel Documents

Jonsone,

Yes you are right, I have to check this.  Just that since the LEFT joins worked perfectly, I thought logically it would the right join to use.
Again, it's obvious I'm pretty new, and my only reference was an online chart for a Java Database course.


Slightwv,
Yes, I noticed, that, but I though it would be better to start a new thread.  Thank you for replying in both areas! :)
I think I have the wrong Column name.
G FGeneral Manager

Author

Commented:
Ah!  I think I MIGHT Have it...   after taking some advice from above (you guys not implying God), here is my new code:


SELECT

traveldocuments.TDNumber,
traveldocuments.TDMoveStart,
traveldocuments.TD_ODR_O_Address,
traveldocuments.TD_ODR_D_Address,
approvals.ApprovalJurisdictionFee,
approvals.ApprovalPermitNowFee,
commodities.CommoditySteerUnitNumber,
commodities.CommodityName,
customers.CustomerName

FROM traveldocuments
LEFT JOIN approvals ON (traveldocuments.Id=approvals.TDId)
LEFT JOIN commodities ON (traveldocuments.Id=commodities.TDId)
LEFT JOIN customers ON (traveldocuments.customerID=customers.Id);



I now get the following output:


MySQL Query Output in HeidiSQL

I think I'm now on the right track!


G
slightwv (䄆 Netminder)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

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);
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Have you tried
SELECT

 traveldocuments.TDNumber,
 traveldocuments.TDMoveStart,
 traveldocuments.TD_ODR_O_Address,
 traveldocuments.TD_ODR_D_Address,
 approvals.ApprovalJurisdictionFee,
 approvals.ApprovalPermitNowFee,
 commodities.CommoditySteerUnitNumber,
 commodities.CommodityName
 customers.CustomerName

 FROM traveldocuments
 INNER JOIN customers ON traveldocuments.CustomerId=customers.Id
 LEFT JOIN approvals ON (traveldocuments.Id=approvals.TDId)
 LEFT JOIN commodities ON (traveldocuments.Id=commodities.TDId);
G FGeneral Manager

Author

Commented:
Ok, so this is the new code now:


SELECT

traveldocuments.TDNumber,
traveldocuments.TDMoveStart,
customers.CustomerName,
commodities.CommoditySteerUnitNumber,
commodities.CommodityName,
traveldocuments.TD_ODR_O_Address,
traveldocuments.TD_ODR_D_Address,
approvals.ApprovalPermitNowFee,
approvals.ApprovalJurisdictionFee,
approvals.ApprovalTotalFee

FROM traveldocuments

LEFT JOIN approvals ON (traveldocuments.Id=approvals.TDId)
LEFT JOIN commodities ON (traveldocuments.Id=commodities.TDId)
LEFT JOIN customers ON (traveldocuments.customerID=customers.Id)

ORDER BY TDNumber DESC;


..and it works pretty sweet....however, I noticed if the Travel document has more than one commodity, it populates my GridView with a Row dedicated to that commodity.

Are there any qualifiers I can add to the SELECT command that will just pull the first record out of the table?
I know there are maybe SQL wildcards or Select Distinct, SQL Case?

Thanks again!
slightwv (䄆 Netminder)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

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;
G FGeneral Manager

Author

Commented:
Hi Slightwv!

Thank you for the response.  So to test it I plugged your code, as is, in to the SQL Query of HeidiSQL, and this is what I got back:


SQL Error
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:

Forgot that some databases require inline views to be aliased.

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  
			) x 
			where rn=1  
		) co  
			ON (t.Id=co.TDId)  
	LEFT JOIN customers cu ON (t.customerID=cu.Id)  
ORDER BY TDNumber DESC;
G FGeneral Manager

Author

Commented:
Thanks slightwv,  so I tried it again and it produced this error:


Error with 'On statement'

Is it possible there is confusion with an alias?  Just that I have other tables that begin with the letter 't' and have an 'Id' field.
slightwv (䄆 Netminder)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

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)  

G FGeneral Manager

Author

Commented:
Hi,

Ok, I tried it and I had to change the approval.TDId to a.TDId, and it worked!

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

Thank you very much!
G FGeneral Manager

Author

Commented:
Thank you everyone!  Part 2 of this question was especially tough!  Thanks for the quick response and great input!
slightwv (䄆 Netminder)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

>> 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;


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;
G FGeneral Manager

Author

Commented:
So, just a quick question, I'll have a chance later to convert the SQL command to code, but just wondering if it will error out on the TravelDocuments that have no commodities attached to them yet, where there is no row 1?   Just your syntax is asking for the first row, should it just be ignored?

G
slightwv (䄆 Netminder)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

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

G FGeneral Manager

Author

Commented:
Just ran your second code, and it worked as well, just had to change aliases for traveldocuments and approvals.

Nice!