Question 20468 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL : #1054 – Unknown column 'messages.app_id' in 'on clause'

I got this MySQL query, I need to join both tables apps and contacts, but I am getting this error:

MySQL said: Documentation

#1054 - Unknown column 'messages.app_id' in 'on clause'

My query:

SELECT
  `messages`.*,
  (
    SELECT
      COUNT(*)
    FROM
      messages AS message
    WHERE
      message.app_id = `messages`.`app_id`
      AND message.contact_id = `messages`.`contact_id`
      AND message.status = "sent"
  ) AS `unread_count`
FROM
  `messages`,
  (
    SELECT
      `contact_id`,
      max(`created_at`) as latest_created_at
    FROM
      `messages`
    GROUP BY
      `contact_id`, `app_id`
  ) AS `max_created_at`
INNER JOIN `apps` AS `apps` ON `messages.app_id` = `apps`.`id` 
INNER JOIN `contacts` AS `contacts` ON `messages.contact_id` = `contacts`.`id` 
WHERE
  `messages`.`app_id` = 1
  AND `messages`.`contact_id` = max_created_at.`contact_id`
  AND `messages`.`created_at` = max_created_at.latest_created_at

what exactly is wrong here?

Tags:

2

Answers


  1. 0

    I think you are simply missing a backtick with the table name –

    SELECT
  `messages`.*,
  (
    SELECT
      COUNT(*)
    FROM
      messages AS message
    WHERE
      message.app_id = `messages`.`app_id`
      AND message.contact_id = `messages`.`contact_id`
      AND message.status = "sent"
  ) AS `unread_count`
FROM
  `messages`,
  (
    SELECT
      `contact_id`,
      max(`created_at`) as latest_created_at
    FROM
      `messages`
    GROUP BY
      `contact_id`, `app_id`
  ) AS `max_created_at`
INNER JOIN `apps` AS `apps` ON `messages`.`app_id` = `apps`.`id`                    -- I have added here
INNER JOIN `contacts` AS `contacts` ON `messages`.`contact_id` = `contacts`.`id`     -- I have added here
WHERE
  `messages`.`app_id` = 1
  AND `messages`.`contact_id` = max_created_at.`contact_id`
  AND `messages`.`created_at` = max_created_at.latest_created_at
    Login or Signup to reply.
  2. 0

    Don’t use old school comma separated table lists, especially not mixed with more "modern" join syntax. You also skipped app_id in your select list and join for max_created_at. The correlated subquery for unread_count is unnecessary as you can add it to max_created_at subquery:

    SELECT
  `messages`.*,
  `max_created_at`.`unread_count`
FROM `messages`
INNER JOIN (
    SELECT
        `contact_id`,
        `app_id`,
        MAX(`created_at`) AS `latest_created_at`,
        COUNT(IF(`status` = 'sent', 1, NULL)) AS `unread_count`
    FROM `messages`
    GROUP BY `contact_id`, `app_id`
) AS `max_created_at`
    ON `messages`.`contact_id` = `max_created_at`.`contact_id`
    AND `messages`.`app_id` = `max_created_at`.`app_id`
    AND `messages`.`created_at` = `max_created_at`.`latest_created_at`
INNER JOIN `apps`
    ON `messages`.`app_id` = `apps`.`id` 
INNER JOIN `contacts`
    ON `messages`.`contact_id` = `contacts`.`id` 
WHERE `messages`.`app_id` = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search