SQL throwing error Unknown column in 'on clause' but field are present there-CodePudding

I am executing the following query and it throwing me an error that

Error Code: 1054. Unknown column 'versant_test_event_log.lis_result_sourcedid' in 'on clause'

But as you can see in query versant_test_event_log table and lis_result_sourcedid both exist there. Then why is it still throwing me this error? Please note I verified the table and I found lis_result_sourcedid are present in the versant_test_event_log table.

SELECT `candidate`.`id`,
       `versant_test_request_logs`.`id`                                    AS
       `versant_test_request_logs.id`,
       `versant_test_request_logs`.`candidate_process_id`                  AS
       `versant_test_request_logs.candidateProcessId`,
       `versant_test_request_logs`.`candidate_id`                          AS
       `versant_test_request_logs.candidateId`,
       `versant_test_request_logs`.`user_id`                               AS
       `versant_test_request_logs.userId`,
       `versant_test_request_logs`.`basiclti_submit`                       AS
       `versant_test_request_logs.basicltiSubmit`,
       `versant_test_request_logs`.`launch_presentation_locale`            AS
       `versant_test_request_logs.launchPresentationLocale`,
       `versant_test_request_logs`.`launch_presentation_return_url`        AS
       `versant_test_request_logs.launchPresentationReturnUrl`,
       `versant_test_request_logs`.`lis_outcome_service_url`               AS
       `versant_test_request_logs.lisOutcomeServiceUrl`,
       `versant_test_request_logs`.`lis_result_sourcedid`                  AS
       `versant_test_request_logs.lisResultSourcedid`,
       `versant_test_request_logs`.`lti_message_type`                      AS
       `versant_test_request_logs.ltiMessageType`,
       `versant_test_request_logs`.`lti_version`                           AS
       `versant_test_request_logs.ltiVersion`,
       `versant_test_request_logs`.`oauth_consumer_key`                    AS
       `versant_test_request_logs.oauthConsumerKey`,
       `versant_test_request_logs`.`oauth_nonce`                           AS
       `versant_test_request_logs.oauthNonce`,
       `versant_test_request_logs`.`oauth_signature_method`                AS
       `versant_test_request_logs.oauthSignatureMethod`,
       `versant_test_request_logs`.`oauth_timestamp`                       AS
       `versant_test_request_logs.oauthTimestamp`,
       `versant_test_request_logs`.`oauth_version`                         AS
       `versant_test_request_logs.oauthVersion`,
       `versant_test_request_logs`.`resource_link_id`                      AS
       `versant_test_request_logs.resourceLinkId`,
       `versant_test_request_logs`.`roles`                                 AS
       `versant_test_request_logs.roles`,
       `versant_test_request_logs`.`tool_consumer_instance_guid`           AS
       `versant_test_request_logs.toolConsumerInstanceGuid`,
       `versant_test_request_logs`.`base_signature_string`                 AS
       `versant_test_request_logs.baseSignatureString`,
       `versant_test_request_logs`.`oauth_signature`                       AS
       `versant_test_request_logs.oauthSignature`,
       `versant_test_request_logs`.`versant_event_ref_id`                  AS
       `versant_test_request_logs.versantEventRefId`,
       `versant_test_request_logs`.`created_by`                            AS
       `versant_test_request_logs.createdBy`,
       `versant_test_request_logs`.`updated_by`                            AS
       `versant_test_request_logs.updatedBy`,
       `versant_test_request_logs`.`created_at`                            AS
       `versant_test_request_logs.created_at`,
       `versant_test_request_logs`.`updated_at`                            AS
       `versant_test_request_logs.updated_at`,
       `versant_test_request_logs->versantTestEventLogs`.`id`              AS
       `versant_test_request_logs.versantTestEventLogs.id`,
       `versant_test_request_logs->versantTestEventLogs`.`progress_status` AS
       `versant_test_request_logs.versantTestEventLogs.progressStatus`,
       `versant_test_request_logs->versantTestEventLogs`.`lis_result_sourcedid` AS
       `versant_test_request_logs.versantTestEventLogs.lisResultSourcedid`
FROM   `candidate` AS `candidate`
       LEFT OUTER JOIN `user` AS `user`
                    ON `candidate`.`user_id` = `user`.`id`
       LEFT OUTER JOIN `versant_test_request_log` AS `versant_test_request_logs`
                    ON `candidate`.`id` =
                       `versant_test_request_logs`.`candidate_id`
       LEFT OUTER JOIN `versant_test_event_log` AS
                       `versant_test_request_logs->versantTestEventLogs`
                    ON `versant_test_event_log`.`lis_result_sourcedid` =
                       `versant_test_request_logs`.`candidate_process_id`
WHERE  `candidate`.`id` = 'b4ff5a17-bdda-4823-a665-906663f4ad61'
LIMIT  0, 10;

versant_test_request_log:

CREATE TABLE `versant_test_request_log` (
  `id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `candidate_id` char(36) NOT NULL,
  `candidate_process_id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `user_id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `basiclti_submit` varchar(30) NOT NULL,
  `launch_presentation_locale` varchar(10) NOT NULL,
  `launch_presentation_return_url` varchar(255) NOT NULL,
  `lis_outcome_service_url` varchar(255) NOT NULL,
  `lis_result_sourcedid` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `lti_message_type` varchar(255) NOT NULL,
  `lti_version` varchar(5) NOT NULL,
  `oauth_consumer_key` varchar(20) NOT NULL,
  `oauth_nonce` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `oauth_signature_method` varchar(15) NOT NULL,
  `oauth_timestamp` bigint(20) NOT NULL,
  `oauth_version` varchar(5) NOT NULL,
  `resource_link_id` varchar(30) NOT NULL,
  `roles` varchar(30) NOT NULL,
  `tool_consumer_instance_guid` varchar(10) NOT NULL,
  `oauth_signature` varchar(40) NOT NULL,
  `base_signature_string` text NOT NULL,
  `created_by` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `updated_by` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `versant_event_ref_id` char(36) DEFAULT NULL,
  PRIMARY KEY (`candidate_id`,`candidate_process_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

versant_test_event_log:

CREATE TABLE `versant_test_event_log` (
  `id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `lis_result_sourcedid` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `tin_id` bigint(20) NOT NULL,
  `imsx_message_identifier` bigint(20) NOT NULL,
  `progress_status` varchar(255) NOT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `overall_score` bigint(20) DEFAULT NULL,
  `sentence_mastery` bigint(20) DEFAULT NULL,
  `vocabulary` bigint(20) DEFAULT NULL,
  `fluency` bigint(20) DEFAULT NULL,
  `pronunciation` bigint(20) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CodePudding user response：

Remove this line

AS versant_test_request_logs.lisResultSourcedid

CodePudding user response：

Use the alias.

create table Table1 (id int, col1 int, col2 int);
create table Table2 (id int, table1_id int, x int)

-- Not using the alias in the ON
select 
`Table One`.`col1` as `Table One.Col1`, 
`Table One->Table Two`.`x` as `Table One->Table Two.X` 
from `Table1` as `Table One`
join `Table2` as `Table One->Table Two` 
  on `Table2`.`table1_id` = `Table One`.`id`

Unknown column 'Table2.table1_id' in 'on clause'

-- Using the alias in the ON
select 
`Table One`.`col1` as `Table One.Col1`, 
`Table One->Table Two`.`x` as `Table One->Table Two.X` 
from `Table1` as `Table One`
join `Table2` as `Table One->Table Two` 
  on `Table One->Table Two`.`table1_id` = `Table One`.`id`

Table One.Col1 | Table One->Table Two.X
-------------: | ---------------------:

-- Using short aliases
select 
t1.`col1` as `Table One.Col1`, 
t2.`x`    as `Table Two.X` 
from `Table1` as t1
join `Table2` as t2
  on t2.`table1_id` = t1.`id`

Table One.Col1 | Table Two.X
-------------: | ----------:

db<>fiddle here