Each time I have to copy a query from MSAccess from a control's [record source] property and paste in notepad to align it just so I can read it. I once used Ctrl+R to replace commas with newline characters but now forgot what the newline character is. Have you used this before, if so how can I get the newline character? Better yet, i...
I was horsing around and figured it would be nice to move my application variables into a table that can be loaded onApplicationStart. My intent is to allow ANT to roll out the app, and change a few settings in the database and the proverbial presto.. In my test code, the application.cfc has a simple query to call all the variable na...
This is a simplified version of a query we are running where we need to find all rows in the main parent table where the child rows match. The query below returns no results when one of the child tables is empty. The main table has two child tables: CREATE TABLE main (id INT PRIMARY KEY, name VARCHAR(8)); CREATE TABLE child1(id INT P...
Hey everyone, I was wondering, if I have some code such as: $result = $db->query($sql); // dont worry, its escaped $myData = (array)$result->fetch_assoc(); where $result->fetch_assoc(); returns a mysqli result object. Is casting it like this right away good practice? I would imagine this is an expensive call - is this true? It...
I'd like to collect the "state of the art" ways to paginate results for any database in this wiki. Input: I have a huge table PAGE_ME: create table PAGE_ME ( ID bigint not null, NAME varchar(32) not null, CREATED TIMESTAMP not null ) id is not necessarily in the same order as created. I want to display the results between...
Hi all so I have the following table, 't1' id r_id o_id count 1 2 100 1 2 3 100 1 3 5 100 1 4 2 101 2 5 3 101 2 6 4 101 2 What I'm trying to do is, for a given list of r_id's, return the r_id and o_id where the count...
What's a good metric for finding the most active forum thread or game in your database? Imagine you run a forum like 4chan. You want the most active threads to appear on the first page. You tried sorting topics by last_updated, but the result is chaotic: the threads you see on each refresh are effectively random, and jumping to the se...
The query I'm running is as follows, however I'm getting this error: #1054 - Unknown column 'guaranteed_postcode' in 'IN/ALL/ANY subquery' SELECT `users`.`first_name`, `users`.`last_name`, `users`.`email`, SUBSTRING(`locations`.`raw`,-6,4) AS `guaranteed_postcode` FROM `users` LEFT OUTER JOIN `locations` ON `users`.`id` = `location...
Can someone please help me out with a query? In Microsoft SQL Server, I have the following query, which executes fine: SELECT * FROM ControlPoint INNER JOIN Project ON ControlPoint.ProjectID = Project.ProjectID INNER JOIN Site ON Site.SiteID = Project.SiteID WHERE Project.ProjectName LIKE '%Flood%' My problem is, when I try to execute...
So what I'm trying to do is a bit tricky. I'm trying to come up with a sql query that will be able to return counts for specified time frames. So I have a table 'v'. Here is some basic data from Table v Table v _____________ id p_id created_at 1 1 2009-06-09 18:54:17 2 2 2009-06-09 21:51:24 3 2 200...
I use the following query to retrieve data from a .mdb file through JDBC, however when I try it on an .odb file it goes does not throw any exceptions but there are no results at all. I am wondering is .odb case sensitive where .mdb is not or is there something else I am missing? "SELECT DISTINCT column-one + ':' + column-two As ResultCo...
I have a INVENTORY table. There are many items inside. When I query it, I just want to retrieve 50 items each time, and continue to get next 50 if user request and repeat this process until end of records. I am using MySQL database. How to do it in plain SQL if possible? thanks, ...
I have 3 models: Books, Notifications, and NotificationTypes. Books have notifications as the Notification model has a book_id. Notifications have one notification_type as the Notification model has one notification_type_id I want all Books created between date1 and date2 books_set1 = Book.find :all, :conditions => ["created_at <= ? A...
I have a pretty large table: 20+ million rows and I need to update about 5% of that - or 1 million rows. Unfortunately, I am updating the (int) column that is being used as the clustered index. My question is: What is the fastest way to update these rows? I have tried updating the rows directly: update t1 set t1.groupId = t2.groupId...
Here is the situation,each page will show 30 topics,so I had execute 1 sql statements at least,besides,I also want to show how many relpies with each topic and who the author is,thus I have to use 30 statements to count the number of replpies and use other 30 statements to find the author.Finally,I got 61 statements,I really worry about ...
Hi, let's assume I have a table with columns such as Cost Rate Repair 12 Repair 223 Wear 1000 Wear 666 Fuel 500 Repair 600 Fuel 450 Wear 400 and I want this data as columns(Repair,Wear,Fuel) as Repa...
Are JOIN queries faster than several queries? (You run your main query, and then you run many other SELECTs based on the results from your main query) I'm asking because JOINing them would complicate A LOT the design of my application If they are faster, can anyone approximate very roughly by how much? If it's 1.5x I don't care, but if...
I have a table of ratings that stores a user ID, object ID, and a score (+1 or -1). Now, when I want to display a list of objects with their total scores, the number of +1 votes, and the number of -1 votes. How can I do this efficiently without having to do SELECT COUNT(*) FROM rating WHERE (score = +/-1 AND object_id = ..)? That's two ...
I have an Access 2007 database that works asynchronously with a MAS200 database. Due to various office politics and red tape, I cannot get access to the MAS200 data, even though ODBC makes it easy, and must import data dumps from MAS200 to Access. Because of this and the way reports are run, I occasionally have duplicate data on a dump...
I have a table with (col1,col2) as a composite primary key. create table twokeytable(col1 int,col2 int,constraint twokeytable_pk primary key (col1,col2)); and another table with col3,col4 collumns witha composite foreign key(col3,col4) which references the(col1,col2) primary key. For some processing I need to drop the foreign key an...