I am working with a MySQL database that has capitalized table/field names like Users, Institutions, etc. Because the operating system of the database host is Linux, identifiers (like the table names) are treated as case sensitive. So, failing to capitalize a table name will result in a table does not exist error. The problem I am trying...
I want to show all cities that have have a count > 5. I have tried to limit my results anything over a count of 5 but it isn't working. SELECT user.city, Count(user.city) AS cnt FROM user Inner Join zip ON zip.zip = user.zip WHERE cnt > 5 GROUP BY user.city WHERE cnt > 5 **<--------------- It fails here** cnt has already been defined ...
Hi, I have a table with 10 million records, what is the fastest way to delete & retain last 30 days. I know this can be done in event scheduler, but my worry is if takes too much time, it might lock the table for much time. It will be great if you can suggest some optimum way. Thanks. ...
Hi! You might have seen that MailChimp has new features to GeoTag users (mark their location) and send a newsletter to all users within 0-50 miles of a fixed point. Their system knows where users are located (via reverse IP lookup), and you can send a newsletter to all people within 50 miles of Downtown Los Angles (for example). I'm int...
Hi there, I am trying to use sql such as this: SELECT t.*, t2.* FROM templates t LEFT JOIN IF(t.t_type = 0,'templates_email', IF(t.t_type = 1,'templates_sms','templates_fax')) t2 ON t.t_id = t2.t_id; Is it possible to do something like that? basically I want to join on one of three tables based on the value from the row. Is this re...
I'm working on an ordering system that works exactly the way Netflix's service works (see end of this question if you're not familiar with Netflix). I have two approaches and I am unsure which approach is the right one; one relies on database polling and the other is event driven. The following two approaches assume this simplified sche...
I have a list of users in my table. How would I go about taking that list and returning it as one PHP variable with each user name separated by a comma? ...
Hi there, Here is the query I am using: SELECT k_id, COUNT(k_id) AS k_count FROM template_keyword_link WHERE k_id IN(1,2,3,4,5) GROUP BY k_id; This query returns something like 1 | 6 2 | 1 3 | 4 4 | 1 5 | 9 I want to add something like AND COUNT(k_id) = 1 so I end up with 2 | 1 4 | 1 However I get invalid use a group function...
I have a "Lock wait timeout exceeded" error from MySQL that I can't reproduce or diagnose. I'm sure it's deadlock (as opposed to a transaction grabbing a lock then twiddling its thumbs), because my logs show that another process started at the same time, also hung, then continued when the first timed out. But normally, InnoDB detects d...
Result needed: The registration number of the cars used by Instructors at the Glasgow, Bearsden office. The 3 relevant tables I have are; ( I have omitted the non-relevant table information, to simplify this. The database is called easydrive. No this is not a School or UNI assignment, it is a question from a text book, that we have bee...
Im pulling information from a database and ive got the query working fine except i'd like to only select conent from a certain date range. Each row has a field with the created date stored as a DATETIME field. What is the basic syntax? ...
I used the following commands to generate 2 fixtures: ./manage.py dumpdata --format=json --indent=4 --natural auth.User > fixtures/user.json ./manage.py dumpdata --format=json --indent=4 --natural --exclude=contenttypes --exclude=auth > fixtures/full.json I've got the following fixture named user.json: [ { "pk": 4, ...
Hi, How to select the first five high number from my table for example: bid_table > 10 customers enter bid, and the query need to give only the five high bid from this 10 customers thanks Yaniv ...
here is my query insert into invoices set Invoice_number = '823N9823', price = '11,768.00', code = 'ret_4_business', created_at = '2010-09-27'; but my price in my db is 11, not 11768 how do i handle money in mysql? the field is a decimal CREATE TABLE `invoices` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Invoice_n...
let say i have one table, which have data like: name status bob single bob single jane null tina null shane married i want if status "single or data null" it means single. so if data empty script can read it as single and can count together. so i can show result like: Single Married 3 ...
MY question deals with month to month data changes..I want a simple (if possible) SQL query that will show the person , and month change grouped by person/month or similar.. the important part is to show gains and losses. For Example I have a table: (date simplified ) id | Date | Person | Sales ---|-----------|--------| ----- 1 ...
Does anybody know what text editor supports a feature which is auto completion for codes in mysql prompt? I really have trouble in finding the answer. I cant find what text editor it is. Im dealing with my research project now. Thanks for replies. ...
hello i have tryed a lot of source codes for this but i have the same error in all of them include 'functii.php'; starts(); opendb(); $query = "SELECT content,`title_real`,`size`,`ext` FROM file WHERE file_id = '3'"; $result = mysql_query($query) or die('Error, query failed'); list($content,$filename,$size,$ext) = mysql_fetch_array($re...
dear all..i have a table it looks like: Name version DDX 01 DTX 05 could i combine two separate fields become one field? and it will show like: Model DDX01 DTX05 thanks before.. ...
Hi, This query pops up in my slow query logs: SELECT COUNT(*) AS ordersCount, SUM(ItemsPrice + COALESCE(extrasPrice, 0.0)) AS totalValue, SUM(ItemsPrice) AS totalValue, SUM(std_delivery_charge) AS totalStdDeliveryCharge, SUM(extra_delivery_charge) AS totalExtraDeliveryCharge, this_.type ...