Let's say I have a table that has "user_id, date, score", and every user has exactly one score every month, but not always on the same day. I want a query that has "user_id, date, score_delta" where score_delta is how much the score will change between "date" and the next month? Am I going to have to do something lame like to_date(to...
I noticed that 'show processlist' on our MySQL server indicates a lot of threads in 'System lock' state, often followed by just 'Locked', the latter which I'd expect since we have some selects locking behind an update/insert on a MyISAM table. But 'System lock' shows up a lot more than just 'Locked' (sometimes adding up to 2 seconds to ...
Hello, Lets say I have a table: -------------------------------------- | ID | DATE | GROUP | RESULT | -------------------------------------- | 1 | 01/06 | Group1 | 12345 | | 2 | 01/05 | Group2 | 54321 | | 3 | 01/04 | Group1 | 11111 | -------------------------------------- I want to order the result by t...
Sorry about a vague title, but here's what i am trying to do. I have the following two tables TABLE_PERSON | TABLE_PHONE | col_Name | col_Name col_phoneID col_phoneNbr -------- | -------- ----------- ------------ Clark Kent | Clark Kent 1 111-111-1111 Bruce Wayne | Clark Kent ...
I have this code in a trigger. if isnull(@d_email,'') <> isnull(@i_email,'') begin update server2.database2.dbo.Table2 set email = @i_email, where user_id = (select user_id from server2.database2.dbo.Table1 where login = @login) end I would like to update a table on another db server, both are MSSQL. the query above works for m...
I am trying to develop a scheduler- and calendar-dependent event application in C#, for which a crucial requirement is to represent recurring events in the database. What is the best way to represent recurring events in a database? More Details: While creating the event I am also sending invites to the certain users and the invitees sh...
I have a SQL table with 11000 keywords in it. I want a query that can find fields which contain a certian letter. So if I include "a" and "b" the query will select all fields which contain the letter "a" and the letter "b" somewhere in the field. ...
I'm writing a forum. I've a TOPICS table, and a POSTS table. In the POSTS table, I have a TopicId field, and a date field. I want to get all the topics, ordered by their last post date, in a single SQL query. How can I do this? EDIT: The post's date is in the POST table. I want to check what is the last post of every post, then checki...
I'm working on a Glass Types database in MS Access. One Table in the DB lists Basic information about the glass: A unique ID (the "glass number"), description, cost per sq. ft., etc. Table 1 "GlassInfo": GlassNo Description CostSqFt TemperCost LamiCost 0001 "Heavy Glass" 1.38 0.18 0.65 0002 "0001 Tempered" 1.50...
The code below looks painstakingly long... but pls if u can spot the error, tell me.. I dont see any duplicates of the alias 'usertns_group' so i wonder why im getting that error.. post LEFT JOIN category2 as postcategory2 ON( post.category2_id = postcategory2.category2_id ) LEFT JOIN category1 as category2category1 ON( postcategory2...
I am attempting to wipe and repopulate test data on SQL CE. I am getting an error due to FK constraints existing. Typically in Sql2005 I would ALTER TABLE [tablename] CHECK/NOCHECK CONSTRAINT ALL to enable/disable all constraints. From what I could find in my searching, it seems that this might not be supported in CE. Is that true? ...
This is based on a similar question How to Replace Multiple Characters in Access SQL? I wrote this since sql server 2005 seems to have a limit on replace() function to 19 replacements inside a where clause. I have the following task: Need to perform a match on a column, and to improve the chances of a match stripping multiple un-neede...
I need to perform a pivot on an XML column in a table, where the XML contains multiple elements with a number of attributes. The attributes in each element is always the same, however the number of elements will vary. Let me give an example... FormEntryId | FormXML | DateCreated =========...
I'm a bit new to Oracle's PL/SQL (using 10g), I was wondering if there's a way to make a private method in an object type, as is often done for private helper methods in other languages (Java, C++, C#, etc...). I know it is possible to make private methods in packages, but I can't seem to find a way to do this for object types. I keep ge...
I am trying to insert rows into a MySQL database from an Access database using SQL Server 2008 SSIS. TITLE: Microsoft SQL Server Management Studio ------------------------------ ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.51a-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL serv...
I've got a mysql plugin that will return a result set in a specified order. Part of the result set includes a foreign key, and I'd like join on that key, while ensuring the order remains the same. If I do something like: select f.id, f.title from sphinx s inner join foo f on s.id = f.id where query='test;f...
For the various popular database systems, how do you list all the columns in a table? ...
I have two date's: 1) 01/05/2009 2) 06/05/2009 How can I know if there's 5 days between them? ...
Hi, all. My intention is to write a graphical query language where users can drag and drop criteria into a GUI query designer, then the designer will generate appropriate SQL query code and return the appropriate result set. Do you know where I can learn more about SQL code generation or if similar tools like this exist? ...
I have a django app that uses MySQL as the database backend. It's been running for a few days now, and I'm up to ID 5000 in some tables already. I'm concerned about what will happen when I overflow the datatype. Is there anyway to tell the auto increment to start over at some point? My data is very volatile, so when I do overflow the ...