Here is the snapshot of the query that doesn't work since I added the Union. SELECT fin05_usager.idUsager, (SELECT sum(nombreReputation) as nombreReputation FROM (SELECT SUM(nombreReputationGagner) as nombreReputation FROM fin05_usager_reputation WHERE fin05_usager_reputation.idUsager =...
Working on parsing a bunch of databases put together in an older, more freewheeling time into a new schema. Basically it's one database per year, with database names like foo98, foo99, foo2000, etc. So for the most recent foo data, I can do something like SELECT foo_person.mdname AS middle_name, ... FROM foo_person, foo_place, foo_thin...
Here's my exported BD table: CREATE TABLE `hta_users` ( `id` int(11) NOT NULL auto_increment, `nombre` varchar(100) collate utf8_spanish_ci NOT NULL, `apellidos` varchar(255) collate utf8_spanish_ci NOT NULL, `nif` varchar(10) collate utf8_spanish_ci NOT NULL, `direccion` varchar(255) collate utf8_spanish_ci NOT NULL, `cp` v...
Hello, I've got some SQL that used to work with an older MySQL version, but after upgrading to a newer MySQL 5 version, I'm getting an error. Here's the SQL: SELECT portfolio.*, projects.*, types.* FROM projects, types LEFT JOIN portfolio ON portfolio.pfProjectID = projects.projectID WHERE projects.projectType = types.typeID AND types.t...
Here is my query: select s.*, u.display_name from wp_wdify_sites s, wp_users u LEFT JOIN wp_wdify_sitesmeta m ON (s.sid = m.site_id) where milestones like '%dateSubmitted%' and milestones not like '%dateArchived%' and u.ID = s.cid and did IN (0) and m.meta_key = 'aboutSite'` The er...
After having solved a previous issue with this query, i'm now stuck with getting this error: 1054 - Unknown column 'calendar_events.jobID ' in 'on clause' I can't undertstand why... and the column defiantly exists! Is it something to do with the WHERE blah AND ... section of the query at the bottom? SELECT calendar_events.* , ...
I have the next query that in my opinion is a valid one, but I keep getting error telling me that there is a proble on "WHERE em.p4 = ue.p3" - Unknown column 'ue.p3' in 'where clause'. This is the query: SELECT DISTINCT ue.p3 FROM table1 AS ue INNER JOIN table2 AS e ON ue.p3 = e.p3 WHERE EXISTS( SELE...
Hello, Trying to use parameter from external query in subquery FROM clause. tbl1: | id | val1 | str1 | | 1 | 12 | sbc | | 2 | 22 | sds | tbl2: | id | val1 | str1 | | 1 | 1 | cp | Trying to write the following query: select * from tbl1 t, (select * from tbl2 where t.id = tbl2.id and tbl2.val1 = 1) tb12; Expected ...
These are my 2 tables: CREATE TABLE `documents` ( `Document_ID` int(10) NOT NULL auto_increment, `Document_FolderID` int(10) NOT NULL, `Document_Name` varchar(150) NOT NULL, PRIMARY KEY (`Document_ID`), KEY `Document_FolderID` (`Document_FolderID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=331 ; CREATE TABLE `fil...
In the following query, the error Unknown column 'order.id' in 'on clause' is being thrown by my order.id reference in the INNER JOIN. The id column does indeed exist. Any ideas why it's not accessible via the sub query? SELECT SUM(price+shipping_price) AS total_sales, COUNT(id) AS total_orders, AVG(price+shipping_price) A...
import MySQLdb import random db = MySQLdb.connect (host = "localhost", user = "python-test", passwd = "python", db = "python-test") cursor = db.cursor() var = .3 sql = "INSERT INTO RandomInt (RAND) VALUES (var)" # RandomInt is the name of the table and Rand is the Column Name cursor.execute(sql) db.c...
I'm working on a messaging application and am trying to find the number of new messages and the date of the most recent messages for all users with whom the current user has active conversations. The following query works, but expanding on it would mean I'd have to run the aggregate subquery twice to get both the count and last sent tim...
I need to do subquery in subquery what causes "Unknown column 't1.product_id' in 'where clause'". It's on line 7. in my example. How to solve this problem? SELECT *,product_id id, (SELECT GROUP_CONCAT (value ORDER By `order` ASC SEPARATOR ', ') FROM ( SELECT `order`,value FROM slud_data LEFT JOIN slud_...
I don't spend a whole lot of time in MySQL, but I have been asked to look into a problem with my church's website. It has been down for quite some time and I am trying to get it back up and running. The original site was done in Mambo 4.5.3, which is an old version. I will upgrade it at some point, but I just want to get it running fo...
Hi, Why am I getting this error: 1054 - Unknown column 't.type' in 'field list' I have a column called type in my table. And I've got the table 'tester' using an alias t. SELECT y.*, (SELECT COUNT(*) FROM (SELECT *, CASE t.type WHEN 'Advanced' THEN t.t...
This sql fails: select * from RRICallouts as r JOIN LevelToCalloutsJT as lc on ( `r.__kp_RecID` = `lc._kf_RecID~Callout` ) JOIN Levels as l ON ( `lc._kf_RecID~Level` = `l.__kp_RecID` ) where `l.__kp_RecID` = 201006221644060009 #1054 - Unknown column 'l.__kp_RecID' in 'where clause This works: select `__kp_RecID` from Le...
Hello all, I have just exported a table using phpMyAdmin and I have tried to import this table into my local database by importing the sql file I have just exported. After phpMyAdmin imports 330 rows of the 15,000. It says: 1054 - Unknown column 'font_name' in 'field list' How can anything go wrong, I thought it was straight for...
i created a user defined sql query that doesn't work. users are supposed to be able to enter search strings in an input field, submit then see the results of their search but everytime i enter a search for something that i know is in the database i get the unknown column "x" in "where clause" error message. would you please help me fix...
I have two tables: books: [isbn, book_title, publisher, ...] inventory: [isbn, date, num_changed] I want to return book titles for those which are on stock. I tried a join (query 1) and got 1054 error, then I substituted the reference with the literal value and now I get 1111 error. query 1: SELECT `books`.`isbn`, `books`.`book_t...
Hello SELECT ka.id, ka.user FROM {auctions} ka WHERE (SELECT MAX(inst) FROM (SELECT SUM(installment) AS inst FROM {payback} kp WHERE id IN (SELECT id FROM {auctions} WHERE user = ka.user) GROUP BY scheduleD...