Hi, I'm having trouble working out a query. I've tried subqueries, different joins and group_concat() but they either don't work or are painfully slow. This may be a bit complicated to explain, but here's the problem: I have a table "item" (with about 2000 products). I have a table "tag" (which contains about 2000 different product tags...
Hello. Been working on this query for some time and I'm having trouble with a selector. I have a properties table and a dates tables. The dates table contains dates (in a DATE and TIMESTAMP format) that are NOT AVAILABLE for the property. The WHERE clause only selects properties that have entries. This is a problem because it should also...
I have a MySQL table with computerid, userid, and few other columns. Now I want a query which would return all the recrods where computerid value is shared by more than one user. Find below sample data: computerid userid 100 5 105 10 110 6 100 7 101 11 100 5 ...
select * from responses brings: 2 | user_name | green,red Can I change the query so that it brings it in the following format?: 2 | user_name |green 2 | user_name |red number of CSV values in third column varies from record to record. ...
I have this simple query: SELECT High, Low FROM prices WHERE Stock = 'XXX' and Date = '2010-02-05' and Low <= 14.88 AND High >= 14.88 Now for 2010-02-05 Low=14.88 and High=15.88 How come the query return empty? It's running on MySQL 5.0.41 Thanks! ...
I'm trying to put together a MYSQL query that will count the number of Non-Null (or better yet, non-zero) values in select fields in a single row and then sort from lowest to highest (based on the count). For example, I have a table with 5 fields... ID, Name, Score_1, Score_2, Score_3. I want to count how many times the value "0" exist...
I have a table with several indexes. All of them contain an specific integer column. I'm moving to mysql 5.1 and about to partition the table by this column. Do I still have to keep this column as key in my indexes or I can remove it since partitioning will take care of searching only in the relevant keys data efficiently without need t...
I have some code that builds SQL queries to perform a lenient, catch-all kind of search on a potentially large table that loooks a little like this // $find_array derived from search string with space or comma used as a delimiter foreach($find_array as $term){ $clauses[]="( OR last_name SOUNDS LIKE '$term' OR first...
Hello i have the following query: SELECT cmfilm.*, cmgenre.titel AS genretitel, cmvertoning.*, cmzaal.titel AS zaaltitel FROM cmfilm LEFT JOIN cmvertoning ON cmvertoning.film_id=cmfilm.id LEFT JOIN cmgenre ON cmfilm.genre_id=cmgenre.id LEFT JOIN cmzaal ON cmvertoning.zaal_id=cmzaal.id GROUP BY cmfilm.t...
Hey I have a query similar to this FROM products AS p, .. LEFT JOIN ( SELECT .. ) AS popularity on popularity.products_id = p.products_id LEFT JOIN ( SELECT .. ) AS reviews on reviews.products_id = p.products_id WHERE.. AND.. .. The nested SELECTs from the LEFT JOIN are static, I mean the returned result is not influenced by external...
I have a table called records which has several columns, one of which is fromphone which represents a phone number. I can run this query to see the different phone numbers: SELECT DISTINCT fromphone FROM records and it shows the different phone numbers. However, if I run this query: SELECT * FROM records WHERE fromphone = '123-456-...
Using Windows Server 2003, MySQL 5.1.23, MySQL .NET Connector 6.2.2 I am trying to use the MySQLBulkLoader class to read in a fixed width text file. An excerpt from that classes Load method is: string sql = BuildSqlCommand(); MySqlCommand cmd = new MySqlCommand(sql, Connection); cmd.CommandTimeout = 10 * 60;// Timeout; cmd.ExecuteNonQ...
I need retrieve data from 2 tables at the same time, the tables are not linked by foreigns keys or such. $query1 = "select idemployee from employee where address like 'Park Avenue, 23421'"; $query2 "select idcompany from company where bossName like 'Peter'"; How can I do this with a kinda thread in PHP?. I've heard that threads ar...
I've been using order by rand() and it's becoming too much of a bottle neck. I've tried this SELECT id FROM users JOIN ( SELECT CEIL( RAND( ) * ( SELECT MAX( id ) FROM users ) ) AS id ) AS r2 USING ( id ) And it would work if all the id's were incremented, however with this particular table, they're not. The id's are fairly rando...
I have a script that loads between ~5000-8000 objects, performs some logic on them and then attempts to insert/update the MySQL table. After about 2500 queries, the database stops executing the queries and the script just hangs with no error thrown. I need advice on what you would do in this situation. The server is a Windows box if th...
Hello! Is it possible to perform a MySQL search and replace while honoring wildcards? eg: $search = "id='%wildcard%-houselisting-rental'>"; $replace = "class='house_rentals'>"; $query = "UPDATE tables SET field = replace(field,'$search','$replace')"; $result = mysql_query($query); I appreciate any advise on the subject -h ...
I have the following query: SELECT id FROM auctions WHERE end_dt > TIME_TO_SEC(TIMEDIFF(end_dt, now())) > '0' GROUP BY auctions.id ORDER BY end_dt ASC LIMIT 15 This is really slow. But basically, we're trying to select the most recent 15 records that are about to "end" by checking if the amount of seconds left is greater than zero. ...
Hey guys, I'm trying to create an inbox messaging system using two tables, one called Message_Topic, and Message. Message_Topic contains all topics created, and the Message table contains the message conversation within each topic. I'm stuck at the selection of all Topics. My Query is as follows: `SELECT umt.intTopicId FROM User uo, ...
Hi, I have this query, running from a PHP page: $feed_sql = "SELECT id, title, description, rssDate FROM feed WHERE MATCH (title) AGAINST ('" . $rows['suburb'] . "') AND NOT EXISTS(SELECT feed_id, recipient_id, issent FROM tracking_table ...
Is an extent used to add new data?what is segment and how is it releted to an extent? ...