2

The following works in SQLite Manager, but doesn't in Python. I get the following error:

sqlite3.OperationalError: no such column: domain_list.short_name

I've tried taking out the "AS domain_list" and referring to just "short_name" and also "websites.short_name" but it still doesn't work in Python. But does in SQLite Manager. It works ok with just the sub query, just not when I join the subquery to the domain_info table.

Any ideas?

SELECT
*
FROM
    (
        SELECT
            websites.short_name 
        FROM
            websites
        INNER JOIN product_info ON product_prices.product_info_id = product_info.id
        WHERE product_info.archive = 1
        GROUP BY
            websites.short_name
    ) AS domain_list
LEFT JOIN
    domain_info
ON
    domain_list.short_name = domain_info.domain
ORDER BY
    last_checked
alj
  • 2,839
  • 5
  • 27
  • 37
  • What are the fields of your domain_list table ? – Louis Jan 11 '12 at 12:27
  • @Louis from the author's post, there should be only one column: `short_name` in that table (alias). – Felix Yan Jan 11 '12 at 12:35
  • There are loads. [id], [domain],[ip_address],[last_checked] and about 8 more, but these are all 'safe' names as far as I can tell. I was wondering whether [domain] might be a reserved word. In the [websites] table there is [id],[url],[short_name]. – alj Jan 11 '12 at 12:51
  • not sure at all about this but do you need to do SELECT websites.short_name AS short_name ? – Shawabawa Jan 11 '12 at 13:46
  • @Shawabawa AHHHHHHHH!!!!!!!!!!!! Yes. You are right. It works. I came across this same problem a year or so ago and had to do the same thing. Thanks. If you put that as the answer I'll accept it. You might as well get the credit for it. Cheers. – alj Jan 11 '12 at 16:14

0 Answers0