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.
ASKER
ASKER
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);
ASKER
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;
ASKER
ASKER
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)
ASKER
ASKER
>> 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;
ASKER
The LEFT JOIN should take care of that. You won't have any "t.Id=co.TDId" so the "rn=1" shouldn't matter.
ASKER
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.
TRUSTED BY
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" ?