MySQL Left Join (Unknown Column)

Question

I am having problems with my query.

MySQL Query:

SELECT DISTINCT(`users`.`username`), `users`.`full_name`, `users`.`profile_picture_url`, 
`users`.`followed_by_count`, `users`.`follows_count`, `users`.`bio`, `users`.`id`
FROM `users`,`interests`
LEFT JOIN `blocked` 
ON `blocked`.`receiver_id` = `users`.`id`
AND `blocked`.`actor_id` = 100 
AND `blocked`.`blocked_reason` = 'Blocked'
WHERE `blocked`.`receiver_id` IS NULL 
AND `users`.`instagram_active` = 1 
AND `users`.`banned` = 0 
AND `interests`.`user_id` = `users`.`id` 
AND `interests`.`interest` = 'Food'
AND `interests`.`active` = 1 
AND `users`.`active` = 1
ORDER BY `users`.`last_login` DESC
LIMIT 0, 25

The error I am getting is this:

1054 - Unknown column 'users.id' in 'on clause'

How is it an unknown column when I am SELECTing it?

I am very confused...

Users:

CREATE TABLE `users` (    
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `instagram_id` int(11) NOT NULL,
 `username` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `bio` text COLLATE utf8_unicode_ci,
 `website` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `profile_picture_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `full_name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 `media_count` int(11) unsigned NOT NULL,
 `followed_by_count` int(11) unsigned NOT NULL,
 `follows_count` int(11) unsigned NOT NULL,
 `last_updated` datetime NOT NULL,
 `last_updated_instagram` datetime NOT NULL,
 `instagram_active` tinyint(1) DEFAULT NULL,
 `last_login` datetime NOT NULL,
 `inserted_on` datetime NOT NULL,
 `banned` tinyint(1) NOT NULL DEFAULT '0',
 `banned_reason` text COLLATE utf8_unicode_ci,
 `oauth_token` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
 `user_level` tinyint(4) NOT NULL,
 `shown_to_others` tinyint(1) NOT NULL DEFAULT '1',
 `credits_offered` tinyint(1) unsigned NOT NULL DEFAULT '2',
 `active` tinyint(1) NOT NULL DEFAULT '1',
 `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `registered_ip` varchar(17) COLLATE utf8_unicode_ci DEFAULT NULL,
 `credits` int(11) NOT NULL,
 `email_notifications` tinyint(1) NOT NULL DEFAULT '1',
 `todays_followers` int(11) NOT NULL DEFAULT '0',
 `todays_followers_hour` int(11) NOT NULL,
 `total_followers` int(11) NOT NULL,
 `credits_yesterday` int(11) NOT NULL,
 `email_is_verified` tinyint(1) NOT NULL DEFAULT '0',
 `email_announcements` tinyint(1) NOT NULL DEFAULT '1',
 `email_credits` tinyint(1) NOT NULL DEFAULT '1',
 `verification_code` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
 `country_id` bigint(20) unsigned DEFAULT NULL,
 `browser_info_id` bigint(20) unsigned DEFAULT NULL,
 `featured_user` tinyint(1) NOT NULL DEFAULT '0',
 `emailed_credits` tinyint(1) NOT NULL DEFAULT '0',
 UNIQUE KEY `id` (`id`),
 UNIQUE KEY `instagram_id` (`instagram_id`),
 KEY `country_id` (`country_id`),
 KEY `browser_info_id` (`browser_info_id`),
 KEY `username` (`username`,`instagram_active`,`banned`),
 CONSTRAINT `users_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT `users_ibfk_2` FOREIGN KEY (`browser_info_id`) REFERENCES `browser_info` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1279 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Interests:

CREATE TABLE `interests` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) unsigned NOT NULL,
 `interest` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
 `inserted_dt` datetime NOT NULL,
 `active` tinyint(1) NOT NULL DEFAULT '1',
 UNIQUE KEY `id` (`id`),
 KEY `user_id` (`user_id`),
 KEY `interest` (`interest`),
 CONSTRAINT `interests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4161 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Blocked:

CREATE TABLE `blocked` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `actor_id` bigint(20) unsigned NOT NULL,
 `receiver_id` bigint(20) unsigned DEFAULT NULL,
 `blocked_reason` enum('Skipped','Blocked') COLLATE utf8_unicode_ci NOT NULL,
 `inserted_dt` datetime NOT NULL,
 `active` tinyint(1) NOT NULL DEFAULT '1',
 `browser_info_id` bigint(20) unsigned DEFAULT NULL,
 UNIQUE KEY `id` (`id`),
 KEY `actor_id` (`actor_id`,`receiver_id`),
 KEY `receiver_id` (`receiver_id`),
 KEY `browser_info_id` (`browser_info_id`),
 CONSTRAINT `blocked_ibfk_1` FOREIGN KEY (`actor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `blocked_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `blocked_ibfk_3` FOREIGN KEY (`browser_info_id`) REFERENCES `browser_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5700 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

As documented under JOIN Syntax:

Join Processing Changes in MySQL 5.0.12

[ deletia ]

• Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

  Example:

  CREATE TABLE t1 (i1 INT, j1 INT);
  CREATE TABLE t2 (i2 INT, j2 INT);
  CREATE TABLE t3 (i3 INT, j3 INT);
  INSERT INTO t1 VALUES(1,1);
  INSERT INTO t2 VALUES(1,1);
  INSERT INTO t3 VALUES(1,1);
  SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

  Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

  SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

  Alternatively, avoid the use of the comma operator and use JOIN instead:

  SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

  This change also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator.

Answer 2

Your from clause is not joining between the tables. If you want the query to parse, try this:

from `users` cross join
     `interests` LEFT JOIN
     `blocked`
     on . . .

Or, better yet, phrase the joins correctly:

from `users` join
     `interests` 
     on `interests`.`user_id` = `users`.`id` LEFT JOIN
     `blocked`
     on . . . 

My advice to you is: Don't use the "," in a FROM statement. It means CROSS JOIN, which is a very expensive operation if you miss the condition in the WHERE clause. The comma is easily missed. You can delete the comma, and the second table becomes an alias for the first -- a very different meaning. Also, don't put join conditions in the WHERE clause. This is what the ON clause is doing.

I am, however, surprised that MySQL generates an error in this case, presumably because you don't have an explicit join between the tables.