sql

When inserting a complex object into an SQL database, when should the object be broken up into its respectful tables?

Edit: In short what strategy should one use on insert and select scripts with complex objects (eg. two select calls, one for each table; a single select call with unions)? We have a database insert (postgresql) that includes a list of objects that is serialized (in text xml), and put it into a cell in a row amongst normal strings and su...

SQL JOIN, GROUP BY on three tables to get totals

I've inherited the following DB design. Tables are: customers customerid customernumber invoices invoiceid amount invoicepayments invoicepaymentid invoiceid paymentid payments paymentid customerid amount My query needs to return invoiceid, the invoice amount (in the invoices table), and the amount due (invoice amount minus any ...

Performance question sql

I'm making a forum. And I'm wondering if i should store the number of replies in the topic table or count the posts of the topic? How much slower will it be if i use sql and count them? Lets say i have a billion posts. Will it be much slower? Im not planning on being that big but what if? How much slower would i be compared to stroing t...

Best way to store large dataset in SQL Server?

I have a dataset which contains a string key field and up to 50 keywords associated with that information. Once the data has been inserted into the database there will be very few writes (INSERTS) but mostly queries for one or more keywords. I have read "Tagsystems: performance tests" which is MySQL based and it seems 2NF appears to be ...

Sqlite : Sql to finding the most complete prefix

I have a sqlite table containing records of variable length number prefixes. I want to be able to find the most complete prefix against another variable length number in the most efficient way: eg. The table contains a column called prefix with the following numbers: 1. 1234 2. 12345 3. 123456 What would be an efficient sqlite query ...

MySql Says Column can't be null for a column that is not null! [using named parameters]

I am trying to execute an INSERT INTO query using .Net through the MySql/.NEt connector. The query makes uses of Parameters. It is rather simple: INSERT INTO post ( ID, content, post_url, blogID, title, addedOn, updatedDate, commentsFeedURL, active, viewCount, commentCount, languageID, authorName, postDate, posRating, negRating, adul...

How should I join these tables?

These are the tables: threads: id, date, title, text comments: id, thread_id, date, comment How would I do to list the last commented thread on top? This is currently how it looks: $threads = mysql_query("SELECT id, title FROM threads ORDER BY date ASC"); while ($thread = mysql_fetch_assoc($threads)) { echo $thread['title']; }...

How to insert a row into a table that has only a single autoincrement column?

My table has only a single column calld id. This column is an autoincrementing key (I am using it for sequencing). I want to do something like: insert into sequencer; but this gives me SQL errors because I guess I need to have a values portion. But there are no other columns in the table and I want the id column to be autoincremented....

Joining on columns of different type?

If one column is of type int and say has a value 10. The other column is of type varchar and has a value of '10'. Is it safe to join on these values (mySql), and would I get the same result as if both were of type int and value 10? I need to have them of different types because in one table the column is an autoincrementing key (so it...

Enforce Referential Integrity on Materialized Path?

I'm trying to implement a tree like structure using a Materialized Path model described here: http://www.dbazine.com/oracle/or-articles/tropashko4. Is it possible to enforce referential integrity on the [path] field? I don't see how SQL could do it, do I have to do it manually in the DAL? ...

Oracle SQL - Strange 'ORA-00907 Missing Right Parenthesis' error

Hi folks, I've written a query to test out a simple linear regression to get the line of best-fit between two sets of weight measures. It should hopefully return results like below, but it's throwing a strange error 'ORA-00907 Missing Right Parenthesis' and TOAD is pointing towards the part where it says: case ( when trn.wid_locati...

Access database query for using month from a date

Hi, I have the following table schema in acess (2007). Date eventDate bit(boolean) lunch, bit(boolean) snacks, bit(boolean) Tea, I would like to have a single query that gives count of luch, snacks and tea (each as a column) for a given month (using the eventdate). Thanks for help. ...

Sql Server : Column wise total SQl Query

Hi All, Is it possible to get column wise total using query? in my grid there are 20 columns. i have to display each columns total value in its footer. now im using TemplateField field and javascript function to get the total value.if it is possible to get it from sql query i can reduce the code ...

C# datatable from sql join 2 columns same name

I have a sql select like so which returns a datatable: select * from table1 a join table2 b on a.id=b.id Both table1 and table2 have a column named itemno . How do i reference the 2 separate columns? Normally I would use something like: datarow["itemno"].ToString(); <=first column named itemno only datarow["b.itemno"].ToString(); <=...

How to do equality comparison in SQL with C#-like behavior?

How to compare equality of value in SQL with null? For those familiar with C#, here are the results of comparing nullable values: null == null : true null == john : false null == paul : false john == null : false john == john : true john == paul : false paul == null : false paul == john : false paul == paul : true The easiest solutio...

Complex SQL query

I have the these tables: - Users - id - Photos - id - user_id - Classifications - id - user_id - photo_id I would like to order Users by the total number of Photos + Classifications which they own. I wrote this query: SELECT users.id, COUNT(photos.id) AS n_photo, COUNT(classifications.id) AS n_classificat...

Storing parametrized definitions of sets of elements and single pass queries to fetch them in SQL

Suppose a database table containing properties of some elements: Table Element (let's say 1 000 000 rows): ElementId Property_1 Property_2 Property_3 ------- ---------- ---------- ---------- 1 abc 1 1 2 bcd 1 2 3 def 2 ...

SQL - Finding differences in row order of two tables

I have two tables of ID's and dates and I want to order both tables by date and see those ids that are not in the same order e.g. table_1 id | date ------------ A 01/01/09 B 02/01/09 C 03/01/09 table_2 id | date ------------ A 01/01/09 B 03/01/09 C 02/01/09 and get the results B C Now...

export query results into CSV file issue in SQL Server

Hello everyone, I am using SQL Server 2008 Enterprise. I want to export the query result into csv file from SQL Server Management Studio. The issue is, the default csv file is comma (',') separated for each result column in each exported row, and I want to make it '\t' separated since comma exists in some of the result column values. A...

SQL Order By list of strings ?

I wish to do a select on a table and order the results by a certain keyword or list of keywords. For example I have a table like so: ID Code 1 Health 2 Freeze 3 Phone 4 Phone 5 Health 6 Hot so rather than just do a simple Order By asc/desc I'd like to order by Health, Phone, Freeze, Hot. Is this possible? Thanks :) ...