[SOLVED] I am "snow blind"

[dough boy]

This query works correctly:

 

SELECT na.article_id, 
           IF((ROUND((total.pages/5))*5) >= total.pages,ROUND((total.pages/5)),ROUND((total.pages/5)+1)) AS total_pages, 
           na.title, 
           na.article_id, 
           na.description, 
           na.added, 
           HOUR(SEC_TO_TIME(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(na.added))) AS hours, 
           MINUTE(SEC_TO_TIME(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(na.added))) AS minutes, 
           mu.user_name, 
           COUNT(nr.user_id) AS ratings, 
           SUM(nr.rating) AS rating, 
           ROUND(AVG(nr.rating),2) AS average, 
           IF(nar.user_id IS NOT NULL,1,0) AS rated 
FROM (news_articles na, main_users mu, 
           (SELECT COUNT(na.article_id) AS pages 
               FROM news_articles na 
             WHERE na.added > '2007-04-02 12:00:25') AS total) 
LEFT JOIN news_ratings nr ON (nr.article_id = na.article_id) 
LEFT JOIN news_ratings nar ON (nar.article_id = nr.article_id AND nar.article_id = na.article_id AND nar.user_id = 1) 
WHERE na.added > '2007-04-02 12:00:25' 
    AND na.user_id = mu.user_id 
GROUP BY na.article_id 
ORDER BY average DESC, added ASC LIMIT 0, 5

 

However, when I want to make that query a "sub" query:

 

SELECT articles.*, 
           COUNT(nc.comment_id) as comments 
FROM (SELECT na.article_id, 
           IF((ROUND((total.pages/5))*5) >= total.pages,ROUND((total.pages/5)),ROUND((total.pages/5)+1)) AS total_pages, 
           na.title, 
           na.article_id, 
           na.description, 
           na.added, 
           HOUR(SEC_TO_TIME(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(na.added))) AS hours, 
           MINUTE(SEC_TO_TIME(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(na.added))) AS minutes, 
           mu.user_name, 
           COUNT(nr.user_id) AS ratings, 
           SUM(nr.rating) AS rating, 
           ROUND(AVG(nr.rating),2) AS average, 
           IF(nar.user_id IS NOT NULL,1,0) AS rated 
FROM (news_articles na, main_users mu, 
           (SELECT COUNT(na.article_id) AS pages 
               FROM news_articles na 
             WHERE na.added > '2007-04-02 12:00:25') AS total) 
LEFT JOIN news_ratings nr ON (nr.article_id = na.article_id) 
LEFT JOIN news_ratings nar ON (nar.article_id = nr.article_id AND nar.article_id = na.article_id AND nar.user_id = 1) 
WHERE na.added > '2007-04-02 12:00:25' 
    AND na.user_id = mu.user_id 
GROUP BY na.article_id 
ORDER BY average DESC, added ASC LIMIT 0, 5) AS articles 
LEFT JOIN news_comments nc ON (nc.article_id = articles.article_id) 

 

I am told "#1060 - Duplicate column name 'article_id'" which is a lovely and helpful error message.

 

Anyone able to shed some light on this?

 

Thanks!

[dough boy]

Ok.  I solved the problem.

 

I added an 'AS a_id' after the 'SELECT na.article_id' and the query works correctly.

 

I don't really understand the problem, but I am guessing that it has something to do with creating a "temp" table within a "temp" table.

 

If someone else has better clarification as to what the problem was, I and I am sure others would appreciate it.

 

Thanks!

[fenway]

I'd love to, but I'm having a hard time following that query myself.

[btherl]

I would guess it's because your original query produces multiple columns with the same column name but different table name.  When you "AS" your subquery, you are creating a conflict, as Mysql doesn't know which one to use.  In postgres it looks like this:

 

live:parking=> \d import_metadata;
     Table "public.import_metadata"
Column |       Type        | Modifiers
--------+-------------------+-----------
name   | character varying | not null
value  | character varying |
Indexes:
    "import_metadata_pkey" primary key, btree (name)

live:parking=> select foo.value from (select * from import_metadata as im1 join import_metadata as im2 using (name)) as foo;
ERROR:  column reference "value" is ambiguous

 

In your case, it's thinking "There's more than one article_id in the subquery results, which one is articles.article_id?"

 

Was it intended that you list na.article_id twice in the select list?

[dough boy]

I'd love to, but I'm having a hard time following that query myself.

 

This little query is nothing.  You should see some of my other queries.   I showed them to one of the MySQL guys once and he was dumbfounded and asked how slow it was.  When I told him .003 he thought I was kidding.

[dough boy]

Was it intended that you list na.article_id twice in the select list?

 

It was intended because I use ADoDB and the getAssoc function to return my results already formatted in a multi-dimensional array.  The first occurance is being used as the primary key, the other is for the reference.  I have a feeling this is what was causing the problem (it doesn't normally, but probably this time because I was using it in a temp table).