How many fields are OK to have in a MySQL table? I have a form to get information from users, it is divided into 6 or 7 unites, but all related to each other. They are about 100 fields. Do you recommend to keep all of them in one table? ...
I am trying to find out when something was added to a database I maintain but the script that adds the date was working. Is there a way to retrieve the date of the original INSERT command? ...
If a user has there deletion field with a value of 1 how can I igonre all there comments and there main comments that have children under them from other users? How would my query look like. Here is my MySQL tables CREATE TABLE articles_comments ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, parent_comment_id INT UNSIGNED NOT NULL, user_id...
I have two Linux/MySQL servers located in the UK, current system timezone on both reports BST (GMT+1) and yet I have found a discrepency in MySQL's output. The following query: SELECT version(), @@time_zone, @@system_time_zone, NOW(), UTC_TIMESTAMP() returns: Server A: 5.0.27-community-nt | SYSTEM | GMT | 2010-10-12 12:17:01 | 2010-...
Hello all, this is actually a followup from a previous question but it contains different information , so I decided to start a new topic. Summary : I have 2 tables one TMP_TABLE and a BKP_TABLE. Both have the same fieldstructure and identical datatypes for the fields( with almost identical data). let's say TMP_TABLE is construc...
Hi All, I want to create a stored procedure which will do matching of two tables. My requirement is to match two tables based on the columns user passes as an input. Syntax: CREATE PROCEDURE reconcile.matchTables(IN TAB1 VARCHAR(25), IN TAB1 VARCHAR(25), IN COLS1 VARCHAR(250) , IN COLS2 VARCHAR(250)) EX: matchTables('table1', 'table2...
Hi, I have a table with SIGNUPS to different events. This table has some simple columns like ID, IDUSER, IDEVENT and DATE. Each event is characterized my a maximum amount of people who can attend, so the table EVENTS has a column called let's say PLACES. What I want to obtain for a given event is the list of the first "PLACES" signups...
I get the following error below and was wondering how can I correct this problem? You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(articles_comments.parent_comment_id = articles_comments.comment_id AND users.' at line 4 Here is my MySQL code. SEL...
I am wondering if there is a way to optimize this query: SELECT Avg(selloff1), YEAR(dt), weekno FROM bbproduct GROUP BY YEAR(dt), weekno Table schema is: CREATE TABLE `bbproduct` ( `id` bigint(20) NOT NULL, `CUSTNO` decimal(6,0) unsigned zerofill NOT NULL default '000000', `DEPTNO` decimal(1,0) uns...
I have a mysql table set up like this id | refference_id | data Is it possible to count the number of entries for each refference_id all in the query ? ...
Why does the date 2010-10-11 24:00:00 give me a MySQL warning? The data is then not written to the database. Warning: #1264 Out of range value adjusted for column 'date' at row 1 ...
hi All, i have found this error on jboss log. im using jboss5.0/mysql application. anyone shed some light. Thanks ERROR [org.jboss.remoting.transport.socket.ServerThread] (WorkerThread#12[59.92.61.55:50642]) WorkerThread#12[59.92.61.55:50642] exception occurred during first invocation java.lang.reflect.InvocationTargetException ...
Hi, I am trying to perform a SELECT query using a GROUP BY clause, however I also need to access data from multiple rows and somehow concatenate it into a single column. Here's what I have so far: SELECT COUNT(v.id) AS quantity, vt.name AS name, vt.cost AS cost, vt.postage_cost AS postage_cost FROM vouchers v INNER JOIN voucher_...
This is a sample list (each line has variable elements) : ['1', 'Tech', 'Code'] ['2', 'Edu'] ['3', 'Money', 'Sum', '176'] I have to insert this into a MySQL table which has 4 columns (max num. of elements in a value in a list). How to do this efficiently? I have a feeling my solution is the least efficient! Here is my solution : f...
Hi guys, I am having trouble removing a column which is indexed. I dont really understand what is happening as I usually have no problem deleting indexes. When I try to delete the column or index I get the following error ERROR 1025 (HY000): Error on rename of './db-name/#sql-135e_9ee6d2' to './db-name/table-name' (errno: 150) If I c...
I have a sql query done by someone. I cannot seem to see the table where query is reading from, but offline, the table is in the same db. The dbconfig is the same offline and online. Is there any way I can find where the table is, or unhide if hidden. Thanks Jean ...
I have a table PAYMENTS in MySql database: CREATE TABLE `PAYMENTS` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `USER_ID` BIGINT(20) NOT NULL, `CATEGORY_ID` BIGINT(20) NOT NULL, `AMOUNT` DOUBLE NULL DEFAULT NULL, PRIMARY KEY (`ID`), INDEX `PAYMENT_INDEX1` (`USER_ID`), INDEX `PAYMENT_INDEX2` (`CATEGORY_ID`), ...
I have this kinda query: SELECT IF(daycode=1,(SELECT...),(SELECT...)) AS weekavg, (SELLOFF1 / weekavg) AS procent FROM ..... it tells me: Unknown column 'weekavg' in 'field list', this happens after I've added the divide, prior to that worked ok. ...
I'm very confused by the subquery function in MySQL. For my example I use 3 tables: adr contains addresses adr_acc contains groups or users that have access to the addresses usr_grp contains the useres belonging to a group select * from adr where adr.adr_id in (select ac1.adr_id from adr_acc as ac1 where ( (ac1.acc_type =...
The error is very simple. There are 3 Tables, Lets name them A, B, C - All of them have 7 rows. A has the following data 1 2 3 4 5 6 7 with Id = 1 B has the following data 8 9 10 11 12 13 14 with Id = 1 C has the following data 15 16 17 18 19 20 21 with Id = 1 Now i am selecting them as : select A.col1,B.col1,C.col1 from...