On 10-06-14 09:13 PM, brian wrote:
Using 5.1.41/Ubuntu
I have 2 tables, members & countries. The former has a column,
country_id (tinyint(3) unsigned). The countries id column is the same
(although auto_increment). I'm trying to select just those countries for
which there is a member. So I decided this query should do the trick:
SELECT c.id, c.name FROM countries AS c INNER JOIN members AS m ON
m.country_id = c.id GROUP BY c.id;
And, indeed, it works like a charm. However, I'm using the CakePHP
framework, which creates a query like:
SELECT `Country`.`id`, `Country`.`name` FROM `countries` AS `Country`
INNER JOIN members AS `Member` ON `Member`.`country_id` = 'Country.id'
WHERE 1 = 1 GROUP BY `Country`.`id`;
This not only gives an empty set, but also throws 171 warnings (more on
that below). I've remove both the WHERE and GROUP BY clauses with no
success. I've been staring at this for an hour now and can't see what
the trouble is. Can any of you?
Solved. I just noticed the quoting difference here:
ON `Member`.`country_id` = 'Country.id'
Country.id is entirely wrapped in single quotes rather than the alias
and column being separately wrapped with back-ticks. I've changed my
code so that the query is created properly.
I'm still curious about the strange warning, though.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
