I'm trying to count my comments and there replies but I can't seem to get it right. Here is my query so far. SELECT posts_comments.*, users.* (SELECT COUNT(*) FROM posts_comments WHERE parent_comment_id >= 1) FROM posts_comments LEFT JOIN users ON posts_comments.user_id = users.user_id WHERE post_id = '" . $post_id . "' AND parent_c...
i have some mysql script for pivot table and then counting some data inside that: SELECT A.Line, week1.1stweek, week2.2ndweek, IFNULL(week1.1stweek,0) + IFNULL(week2.2ndweek,0) AS TOTAL FROM inspection_report AS A LEFT JOIN( SELECT Line, (SUM(S) + SUM(A) + SUM(B)*0.4 + SUM(C)*0.1)/COUNT(Serial_number) AS 1stweek...
I keep getting this error: Result consisted of more than one row I have this function: DROP FUNCTION IF EXISTS db.GetUserIDByCourseID; CREATE FUNCTION db.`GetUserIDByCourseID`(CourseID int) RETURNS int(11) BEGIN SELECT (c.user_id + COALESCE(pi.user_id, 0) + COALESCE(p.user_id, 0)) INTO @user_id FROM courses c ...
I want to TRIM(LEADING 0 FROM month) FROM table1 where table1 has column month with data formatted like so: 01, 02, 03 I would like to update the data so it is formatted as: 1, 2, 3, ... Thanks! ...
Hi, i have a table that am trying to filter a particular ID (rid) that falls between particular dates....but! what am getting is a query that doesn't consider the ID (rid) as condition. This is the query: SELECT * FROM booking WHERE '2010-10-01' BETWEEN datefrom AND dateto - INTERVAL 1 DAY OR '2010-10-09' BETWEEN datefrom + I...
In my asp.net mvc application, I am using MYSQLMembershipProvider. I want to read the connection string of Membership provider at run time from external file. I am using MYSQL Connectore 6.3.4 How can I read the connection string from external file for MYSQLMemberShipProvider???? Please suggest me the way. Thanks Aayushi ...
SQL query: ALTER TABLE `x` CHANGE `y` `y` INT( 9 ) UNSIGNED NOT NULL DEFAULT '1000' AUTO_INCREMENT MySQL said: Documentation 1067 - Invalid default value for 'y' whether the default value can be combined with auto increment in mysql What is the correct MySQL syntax? ...
What database structure to use to store GEDCOM information? ...
One database connection is equal to one web request (in case, of course, your client reads the database on each request). By using a connection pool these connections are pre-created, but they are still used one-per-request. Now to some numbers - if you google for "Tomcat concurrent connections" or "Apache concurrent connections", you'l...
I have a table with date ranges. I want to look for gaps between these ranges. I've figured out that I may left join table to itself and calculate the difference. Current table: date_begin date_end 2010-08-01 2010-08-15 2010-08-16 2010-08-30 2010-08-31 2010-09-12 2010-10-01 2010-10-15 I want to get: date_begin date...
Hello, I know it is not an appropriate technique to have a structure of MySQL table as such, but I have to work with such. The problem is, that the field in table has value with comma seperated integers, like "1,3,5,7,10" and I want the query to return rows, in which field has a to the query passed number in it, like: SELECT * FROM `tab...
i want to make some daily report which order by date. i want this data can increase every day. Date qty QP 2010-09-01 10 10 2010-09-02 3 13 (it means result QP from 2010-09-01 accumulate with 2010-09-02) 2010-09-03 8 21 this is the 1st code: SELECT Date, SUM(number) AS qty FROM...
Hello, I am using a page where a variable $submissionid is being posted to it. I would like to use this variable and pull the field subcheck from a MySQL table called submission. I would like the value of the field subcheck to simply be a new variable $subcheck. I'm not sure how to do this. I have a query below, but how to I convert...
Hello, I have updated to rails 3 on my Snow leopard. Previously i use sqlite for development. It's working fine when i rake db:create. Now I'm trying to install mysql on my mac. I downloaded mysql-5.1.50-osx10.6-x86.dmg Installed all three file.(mysql-{version}-osx10.5-x86.pkg, MySQL.prefPane, MySQLStartupItem.pkg) execute this to in...
Using the ADO.NET MySQL Connector, what is a good way to fetch lots of records (1000+) by primary key? I have a table with just a few small columns, and a VARCHAR(128) primary key. Currently it has about 100k entries, but this will become more in the future. In the beginning, I thought I would use the SQL IN statement: SELECT * FROM `...
I have a search box that the user can select a $location a $type and a $rating. $result = mysql_query("SELECT * FROM Places WHERE Location = '$location' and Type ='$type' and Rating = '$rating'") or die(mysql_error()); This works fine if the user selects and option from all 3 drop down boxes- however how do I make the msql query che...
I need to differentiate between nodes starting with 'O' and nodes starting with 'Ö' (o umlaut). The problem is that since the node table and the title column have utf8_general_ci collation, MYSQL does not treat o and ö umlaut differently and this query returns nodes starting with O AND nodes starting with Ö SELECT node.nid AS nid FROM ...
I am trying to create a single query for getting information from the main table (WordPress user table), and the user meta table. If you're not familiar with WP DB architecture: wp_users holds basic user information and their IDs, wp_usermeta holds the ID, meta_key, and meta_value. Let's say I want to get all users that have certai...
Hello, which is the best primary key to store website address and page URLs? To avoid the use of autoincremental id (which is not really tied to the data), I designed the schema with the use of a SHA1 signature of the URL as primary key. This approach is useful in many ways: for example I don't need to read the last_id from the databa...
My query is "select column_name,table_name from information_schema.columns". This query returns a number of columns and tables. Now, I want to determine the name of the database that corresponds to the returned table names. How do I accomplish this? ...