Need help finding problem with query. 2

[thepixel]

My query returns with this error:
Unknown column 'u.user_id' in 'on clause'

Here's the query:

SELECT u.user_id AS id, a.affiliate_id AS a_id,
                u.phone, u.city, u.state, u.email,
                a.company, a.username, a.contact, a.address, a.zip, a.subscribed,
                i.image_id as storefront
        FROM `user` u, affiliate a
        LEFT JOIN image i ON i.user_id = u.user_id
        WHERE a.affiliate_id='1' AND u.affiliate_id = a.affiliate_id AND i.deleted IS NULL;

Here's the table schema:

CREATE TABLE `user` (                                                           
     `user_id` int(11) NOT NULL auto_increment,
     `affiliate_id` int(10) default '0',
     `firstname` varchar(100) default '',
     `lastname` varchar(100) default '',
     `city` varchar(100) NOT NULL default '',
     `state` varchar(2) NOT NULL default '',
     `phone` varchar(20) NOT NULL default '',
     `email` varchar(255) NOT NULL default '',
     `password` varchar(50) NOT NULL default '',
     `role_id` int(10) NOT NULL default '2',
     `hash` varchar(50) NOT NULL default '',
     `date` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
     `verified` int(11) NOT NULL default '0',
     PRIMARY KEY (`user_id`),
     UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

My table clearly has a user_id column. I cannot figure out why i continue to get this error.

 

[thepixel]

the strange thing is this was working on the testing server. and now that i've moved it to the live server with the exact same table schema/data, it throws this error. I've checked to make sure I'm using the correct Database. Select statements are working elsewhere on the site. Not sure what's going on here.

 

[r937]

your problem lies here:

affiliate a
LEFT JOIN image i 
ON i.user_id = u.user_id

as you can see, the ON clause to join the a and i tables references a column that isn't in either table


when you mix comma joins with JOIN syntax, the JOIN syntax joins take precedence in the evaluation (just like ANDs have precedence over ORs)

solution: rewrite your queries to use JOIN syntax only

change this --

FROM `user` u, affiliate a
LEFT JOIN image i ON i.user_id = u.user_id
WHERE a.affiliate_id='1' 
AND u.affiliate_id = a.affiliate_id 
AND i.deleted IS NULL;

to this --

  FROM affiliate AS a
INNER
  JOIN `user` AS u
    ON u.affiliate_id  = a.affiliate_id 
LEFT OUTER
  JOIN image i 
    ON i.user_id = u.user_id
 WHERE a.affiliate_id = 1 
   AND i.deleted IS NULL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[r937]

by the way, your test server is version 4.something, while your live server is 5.something

tightening up the syntax rules which generate this error message occurred in version 5

you aren't the first one to have this problem

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[thepixel]

Thanks a bunch rudy! Thanks also for explaining what's going on. it makes a lot more sense now, and I have learned something new as a result of it! I appreciate it!

 

[whosrdaddy]

rudy,

you are the best, have a star!

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[thepixel]

Ok. I'm not exaclty sure if I'm doing this right. I want to select [these fields] from affiliate and join `user` (there should always be a corresponding record on user and it should not return anything if there isn't), then I want to join image (where there may or may not be an image, but it shouldn't affect the number of results returned [there should only be one image if any]) My query is (thanks to rudy):

SELECT u.user_id AS id, a.affiliate_id AS a_id,
 		u.phone, u.city, u.state, u.email,
 		a.company, a.username, a.contact, a.address, a.zip, a.subscribed,
		i.image_id as storefront
	FROM `affiliate` AS `a`
	INNER JOIN `user` AS `u` ON u.affiliate_id = a.affiliate_id
	LEFT OUTER JOIN `image` AS `i` ON i.user_id = u.user_id
	WHERE a.affiliate_id = 1 AND i.deleted IS NULL;

The issue i'm running into is it returns 0 results as is, but when I remove any references to the image table, it returns the 1 row I was looking for? Am I doing this right?

 

[r937]

change the word WHERE to AND, thus moving those two additional conditions into the ON clause



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[thepixel]

Thanks again Rudy!

 

[r937]

oops, on second thought, leave [blue]WHERE a.affiliate_id = 1[/blue] as the WHERE clause, and move only [blue]AND i.deleted IS NULL[/blue] to the ON clause

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[thepixel]

Ha. I just came back to post a response because I was getting 2 results instead of just one. Thanks again.