sql

Embarassingly easy SQL question(s)

I am trying to write what should ostensibly, be relative easy SQL queries - yet, I cant seeem to get them to work. Is it possible to write a query that looks something like this: select t.name from (select * from mystoredproc(1,2,3) as t) where t.name = 'foobar' Two problems with the above query: 1) First off all, th...

How do I insert datetime value into a SQLite database?

I am trying to insert a datetime value into a SQLite database. It seems to be sucsessful but when I try to retrieve the value there is an error: <Unable to read data> The SQL statements are: create table myTable (name varchar(25), myDate DATETIME) insert into myTable (name,mydate) Values ('fred','jan 1 2009 13:22:15') ...

How best to represent rational numbers in SQL Server?

I'm working with data that is natively supplied as rational numbers. I have a slick generic C# class which beautifully represents this data in C# and allows conversion to many other forms. Unfortunately, when I turn around and want to store this in SQL, I've got a couple solutions in mind but none of them are very satisfying. Here is an...

Imposing an order on related (via a foreign key) objects

Ok, imagine a typical scenario, - there is a thread object and comments, attached to it. It can be expressed in django ORM's terms very roughly like this: class Thread(models.Model): id = models.AutoField(primary_key=True) name = models.CharField(max_length=255) class Comment(models.Model): id = models.AutoField(primary_key...

How do I select rows in table (A) sharing the same foreign key (itemId) where multiple rows in table have the values in table B

Sorry about the title, not sure how to describe without example. I trying to implement faceting of attributes in SQL Server 2008. I have 2 tables. itemAttributes and facetParameters Assume the following values in itemAttributes id, itemId, name, value --------------------------------------- 1 1 keywords example1...

Return only unique table id's from MySQL join

I have a MySQL database with two tables (simplified for this question): movies table columns: id, title, duration, description, created_at, updated_at rentals table columns: id, movie_id, status, created_at, updated_at The rental status is either 'CHECKED OUT' or 'RETURNED', so a movie is available if it either has no associated rental,...

C# Update Table using SqlCommand.Parameters

I'm trying to update an MSSQL table using SqlCommand, I think it's a syntax error with my T-SQL, but here is what I have so far: SqlCommand sqlCmd = new SqlCommand("UPDATE yak_tickets SET email = @emailParam, subject = @subjectParam, text = @textParam, statusid = @statusIDParam, ticketClass = @ticketClassParam WHERE id = @ticketIDParam"...

#Error showing up in multiple LEFT JOIN statement Access query when value should be NULL

I'm trying to return an ID's last 4 years of data, if existing. The table (call it A_TABLE) looks like this: ID, Year, Val The idea behind the query is this: for each ID/Year in the table, LEFT JOIN with Year-1, Year-2, and Year-3 (to get 4 years of data) and then return Val for each year. Here's the SQL: SELECT a.ID, a.year AS [Year],...

Linq-to-SQL throwing NullReferenceException despite checking for null arguments

I'm trying to write Linq-To-SQL queries in LinqPad to help migrate users from old tables to new tables. Part of this migration is storing all addresses in a separate table. I'm using the following query to determine if a user's address exists in the new table (so I don't have duplicate entries): var addresses = from a in Addresses where...

is it invalid to set a reference to null or 0?

I'll be doing this in sqlite now that they support foreign keys and tsql and perhaps mysql. Is it illegal to do something like CREATE TABLE comment( id integer primary key, parent integer references(comment.id), author integer references(User.id), desc varchar(max), hidden bit deleted bit ); where parent may be 0 or null because it ...

How to write this MySQL update statement?

How to write this MySQL update statement: table1 identity table2 memberid, username, email Some values in identity of table1 are email, some are username, how to replace the values in identity of table1 with the corresponding value of memberid of table2? ...

Why does mysql_num_rows($result) return 1 even if $result returns empty result set?

Why does mysql_num_rows($result) return 1 even if $result returns empty result set? $resut=mysql_query("select * from tablename where column1='$memberid' and (TIME_TO_SEC(TIMEDIFF(NOW(),when_submit))/60<2)")or die(mysql_error()); $count=mysql_num_rows($result); when I echo $count, I get 1 . ...

No full-text support; must have effective mysql db search engine; where to find one?

I have asked several questions about Zend and its search functions. Now after further reading I have noticed that it requires FULL-TEXT indexes in the MySQL fields. My webhosting provider doesn't allow me to change anything in the my.ini (my.cnf) file, which holds information about minimum length of word to search full-text indexes an...

MySQL - A query based on another query

I write a lot of queries resembling the query example code below. I was wondering whether there was a more efficient code/script? $query1 ="SELECT * FROM table1 WHERE date >= '$todaysdate' "; $result1 = mysql_query($query1) or die ("Error in query: $query1. " . mysql_error()); if (mysql_num_rows($result1) > 0) { while($row1 = m...

SQL Server: How do I get the value for the row I inserted?

My SQL Server table has, between other, these columns. AutoID which has IdentitySpecification set to True and GuidKey which has the default value of (newid()) AutoID GuidKey 1 f4rc-erdd 2 gedd-rrds Is there any way of getting the GuidKey from the row inserted? I need something like the Scope_Identity(), with the di...

Rails find_by_sql - how to run generic queries

I need to execute this query to find out the next auto_increment value that will be used by MySQL for a particular table. find_by_sql ["select auto_increment from information_schema.tables where table_schema = ? and table_name = ? and auto_increment is not null", db_name, tbl_name] How to invoke this particular query? This works o...

Does creating a FK automatically index the column?

I am using mySQL 5.1 and am wondering, is it necessary to add CREATE INDEX syntax on a column after you have defined it in a FK relationship. Common sense tells me that if a column is a key (foreign or otherwise), then it needs to be indexed - but you never know ... Do I have to EXPLICITLY create an index on my FK - or does creating an ...

mysql query if statement

To keep this simple, let's say I'm making a basic pageview counter in php that stores the counts for each page in a mysql table. The table had 2 colums: PAGE_ID and COUNT. I added the following code to every page: $query = "INSERT INTO table VALUES ('$page_id', '1') ON duplicate KEY UPDATE COUNT=COUNT+1"; $result = mysqli_que...

On which field was the keyword found? SQL Searching

I'm trying to make a detailed search engine for my web site. The keywords are being searched in multiple fields and tables. For example, using keywords: uludag university These keywords are being searched in the education, address, contactname, and contactsurname fields in my Members table. I have to do it so because there must b...

Partitioning a database table in MySQL

I am writing a data warehouse, using MySQL as the back-end. I need to partition a table based on two integer IDs and a name string. A more concrete example would be to assume that I am storing data about a school. I want to partition the school_data table based on COMPOSITE 'Key' based on the following: school id (integer) course_id (i...