SQL Error when attempting to create a three Table Join state

G F

6y797

Hi,

I would like to create a MySQL Select Statement,using Joins to join 3 tables.

Currently in my Code, I have the two Table join working great:

SELECT
traveldocuments.TDNumber,
traveldocuments.TDMoveStart,
traveldocuments.TD_ODR_O_Address,
traveldocuments.TD_ODR_D_Address,
approvals.ApprovalJurisdictionFee,
approvals.ApprovalPermitFee
FROM traveldocuments
Left JOIN approvals
ON (traveldocuments.Id=approvals.TDId);

So I was doing some searching, and I can see examples from other languages (Java), that I should be able to just add on the third table, but it errors out (not in code but in the DBMS), this is what I tried to change it to:

SELECT
traveldocuments.TDNumber,
traveldocuments.TDMoveStart,
traveldocuments.TD_ODR_O_Address,
traveldocuments.TD_ODR_D_Address,
approvals.ApprovalJurisdictionFee,
approvals.ApprovalPermitNowFee,
commodities.CommoditySteerUnitNumber
FROM traveldocuments
Left JOIN approvals
ON (traveldocuments.Id=approvals.TDId);
LEFT JOIN commodities
ON (traveldocuments.Id=commodities.TDId);

The specific error is Unknown column : 'commodities.CommoditySeerUnitNumber' in 'field list'

I know this table and column exist, when I'm building the statement in HeidiSQL, it autocompletes the column name, and I've printed out my table columns and I can see it there.

I have a Foreign Key in the commodities table setup exactly the same way as the approvals table, it's almost like a parent/child setup.

Any suggestions would be appreciated.

Thank you!

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

G F6y ago
Hi, I found the issue, it was the first semi colon, after the initial join, it has to be taken out.
Thanks!
G F6y ago
Hi, yes it is 'Steer', I made a type in the error, sorry about that.

Here is the Code for my Table, I hope this helps:

CREATE TABLE `commodities` (
`Id` INT(11) NOT NULL AUTO_INCREMENT,
`TDId` INT(11) NOT NULL,
`CommodityName` VARCHAR(50) NOT NULL,
`CommodityMeasureType` VARCHAR(10) NOT NULL,
`CommodityWidth` VARCHAR(20) NOT NULL,
`CommodityLength` VARCHAR(20) NOT NULL,
`CommodityHeight` VARCHAR(20) NOT NULL,
`CommoditySpacings` VARCHAR(20) NOT NULL,
`CommodityNotes` VARCHAR(500) NOT NULL,
`CommodityUnitWeightType` VARCHAR(10) NOT NULL,
`CommoditySteerUnitNumber` VARCHAR(20) NOT NULL,
`CommoditySteerPlateNumber` VARCHAR(20) NOT NULL,
`CommoditySteerJurisdiction` VARCHAR(50) NOT NULL,
`CommoditySteerAxles` VARCHAR(20) NOT NULL,
`CommoditySteerWheels` VARCHAR(20) NOT NULL,
`CommoditySteerTireSize` VARCHAR(20) NOT NULL,
`CommoditySteerWeights` VARCHAR(20) NOT NULL,
`CommodityDriveUnitNumber` VARCHAR(20) NOT NULL,
`CommodityDrivePlateNumber` VARCHAR(20) NOT NULL,
`CommodityDriveJurisdiction` VARCHAR(50) NOT NULL,
`CommodityDriveAxles` VARCHAR(20) NOT NULL,
`CommodityDriveWheels` VARCHAR(20) NOT NULL,
`CommodityDriveTireSize` VARCHAR(20) NOT NULL,
`CommodityDriveWeights` VARCHAR(20) NOT NULL,
`CommodityTrailerUnitNumber` VARCHAR(20) NOT NULL,
`CommodityTrailerPlateNumber` VARCHAR(20) NOT NULL,
`CommodityTrailerJurisdiction` VARCHAR(50) NOT NULL,
`CommodityTrailerAxles` VARCHAR(20) NOT NULL,
`CommodityTrailerWheels` VARCHAR(20) NOT NULL,
`CommodityTrailerTireSize` VARCHAR(20) NOT NULL,
`CommodityTrailerWeights` VARCHAR(20) NOT NULL,
PRIMARY KEY (`Id`),
INDEX `FK_commodities_traveldocuments` (`TDId`),
CONSTRAINT `FK_commodities_traveldocuments` FOREIGN KEY (`TDId`) REFERENCES `traveldocuments` (`Id`) ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=10
;
I'm also including a screen shot of the top of the Table, so you can see the references.

Thank you!
G
Jose Hernandez6y ago
can you provide your definition for commodities table?

and also check for typos : your error says 'CommoditySeerUnitNumber' and your select statement says 'CommoditySteerUnitNumber', there is a diference Seer/Steer.