sql

In MySQL, what is the most effective query design for joining large tables with many to many relationships between the join predicates?

In our application, we collect data on automotive engine performance -- basically source data on engine performance based on the engine type, the vehicle running it and the engine design. Currently, the basis for new row inserts is an engine on-off period; we monitor performance variables based on a change in engine state from active to...

Many to many table design question

Originally I had two tables in my DB, [Property] and [Employee]. Each employee can have one "Home Property" so the employee table has a HomePropertyID FK field to Property. Later I needed to model the situation where despite having only one "Home Property" the employee did work at or cover for multiple properties. So I created an [Emp...

How to check the existence of a row in SQLite with Python?

I have the cursor with the query statement as follows: cursor.execute("select rowid from components where name = ?", (name,)) I want to check for the existence of the components: name and return to a python variable. How do I do that? ...

mysql select two prior to and one after NOW()

Hi I have a table with a column event_time. How can I select two rows right before NOW() and the next one after NOW(), ordered by event_time? Is is possible with a single query? ...

SQL join different tables depending on row information

Suppose I have table A with a field that can be either 1 or 2... How do I select such that for each row in table A, if the field is 1, join the select with table B and if the field is 2, join the select with table C? ...

Input Sanitation Best Practices

Our team has recently been working on a logic and data layer for our database. We were not approved to utilize Entity or Linq to SQL for the data layer. It was primarily built by hand. A lot of the SQL is auto generated. An obvious down fall of this is the need to sanitize inputs prior to retrieval and insertion. What are the best meth...

Speeding up inner-joins and subqueries while restricting row size and table membership

I'm developing an rss feed reader that uses a bayesian filter to filter out boring blog posts. The Stream table is meant to act as a FIFO buffer from which the webapp will consume 'entries'. I use it to store the temporary relationship between entries, users and bayesian filter classifications. After a user marks an entry as read, it w...

Visual Representation Of Database Schema

is there some standard about how to graphically represent database schemas? Is UML a defacto standard for this? Also, is there some free tool that can help me convert an sql file (basically bunch of CREATE TABLE queries) into some nice graph (UML or not UML)? ...

sql statement supposed to have 2 distinct rows, but only 1 is returned

I have an sql statement that is supposed to return 2 rows. the first with psychological_id = 1, and the second, psychological_id = 2. here is the sql statement select * from psychological where patient_id = 12 and symptom = 'delire'; But with this code, with which I populate an array list with what is supposed to be 2 different rows,...

Easy C library to access MySQL

Any suggestions for a real simple C library to query a single MySQL table, nothing fancy here. Just doing a single select * from a table. Any help is appreciated. ...

sql - HAVING at least 10

i would like to display results for values that are only 10 and over select name, count(*) from actor join casting on casting.actorid = actor.id where casting.ord = 1 group by name order by 2 desc that will return this: name count(*) Sean Connery 19 Harrison Ford 19 Robert De Niro 18 Sylvester Stallone 18 etc but i wan...

How to know the names of other users in oracle10g?

Hi guys I want to know how can we know the other users of Oracle 10g on the same system? Is there any query for that? Thank you!!! ...

Restrict varchar() column to specific values?

Is there a way to specify, for example 4 distinct values for a varchar column in MS SQL Server 2008? For example, I need a column called Frequency (varchar) that only accepts 'Daily', 'Weekly', 'Monthly', 'Yearly' as possible values Is this possible to set within the SQL Server Management Studio when creating the table? ...

Optimising SQL distance query

I'm running an MySQL query that returns results based on location. However I have noticed recently that its really slowing down my PHP app. I used CodeIgniter and the profiler shows the query taking 4.2seconds. The geoname table has 500,000 rows. I have some indexes on the key columns, how else can speed up this query? Here is my SQL: ...

Getting all parent rows in one SQL query

I have a simple MySQL table thats contains a list of categories, level is determined by parent_id: id name parent_id --------------------------- 1 Home 0 2 About 1 3 Contact 1 4 Legal 2 5 Privacy 4 6 Products 1 7 Support 1 I'm attempting to make a breadcrumb trail. So i have the 'id'...

How to limit results by SUM

I have a table of events called event. For the purpose of this question it only has one field called date. The following query returns me a number of events that are happening on each date for the next 14 days: SELECT DATE_FORMAT( ev.date, '%Y-%m-%d' ) as short_date, count(*) as date_count FROM event ev WHERE ev.date >= NOW() GR...

How to get size of file uploaded to SQL-Server?

Is there a way to tell how to get a file size that is uploaded to database? SELECT [ID] ,[File] FROM [dbo].[Reports] I would like to be able to tell user the size of File which is VarBinary(max) field in MS SQL 2005/2008. How to do that? Maybe the only way to do is to create another column and when inserting file i should al...

Change an autoincrementing field to one previous

One day, wordpress suddenly jumped from pots id 9110 to 890000000 post. Days later I'd like to move back new posts to continue from id 9111. I'm sure that id will never reach id 890000000, no problem here, but id is an autoincrement field and ALTER TABLE wp8_posts AUTO_INCREMENT = 9111 is not working. Can I force id to continue from 911...

Problems with :uniq => true/Distinct option in a has_many_through association w/ named scope (Rails)

See updates at bottom of question. I had to make some tweaks to my app to add new functionality, and my changes seem to have broken the :uniq option that was previously working perfectly. Here's the set up: #User.rb has_many :products, :through => :seasons, :uniq => true has_many :varieties, :through => :seasons, :uniq => true has_ma...

How does one escape an apostrophe in db2 sql

I'm looking for the db2 equivalent of T-SQL's: INSERT INTO People (Surname) VALUES ('O''Hara'); ...