database-queries

Align microsoft access queries

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...

Setting Coldfusion dynamic application variables from a query

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...

Why does this query only return results with non-empty child tables?

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...

Is casting a mysqli result object to an array good practice?

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...

Page results in [database of your choice]

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...

MySql Group by

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...

Finding most active topics or games

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...

Using column alias in WHERE clause of MySQL query produces an error

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...

Microsoft Access SQL query

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...

MySQL: Retrieving count of records from a particular time frame

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...

Different results from .mdb vs .odb, why?

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...

how to repeatedly retrieve certain amount of data from a large data set?

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, ...

SQL noob needs help writing a Rails query involving joins and maybe intersect

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...

How to speed up a massive update to the clustered column?

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...

What is the best way to reduce sql queries in my situation

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 ...

Converting Rows to column

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...

MySQL: JOIN queries vs multiple queries

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...

Optimally querying a database of ratings?

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 ...

Table Merge Without Dupes

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...

does the order a composite key is defined matter?

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...