Avatar of G F
G FFlag for Canada

asked on 

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

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
MySQL ServerSQL

Avatar of undefined
Last Comment
G F
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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" ?
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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.


Avatar of awking00
awking00
Flag of United States of America image

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.
Avatar of G F
G F
Flag of Canada image

ASKER

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:


User generated image

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.
Avatar of G F
G F
Flag of Canada image

ASKER

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:


User generated image

I think I'm now on the right track!


G

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

Open in new window

Avatar of awking00
awking00
Flag of United States of America image

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);
Avatar of G F
G F
Flag of Canada image

ASKER

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!

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; 

Open in new window

Avatar of G F
G F
Flag of Canada image

ASKER

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:


User generated image
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of G F
G F
Flag of Canada image

ASKER

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


User generated image

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.

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)  

Avatar of G F
G F
Flag of Canada image

ASKER

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!
Avatar of G F
G F
Flag of Canada image

ASKER

Thank you everyone!  Part 2 of this question was especially tough!  Thanks for the quick response and great input!

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

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

Open in new window

Avatar of G F
G F
Flag of Canada image

ASKER

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

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

Avatar of G F
G F
Flag of Canada image

ASKER

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

Nice!
SQL
SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo