Batch Select Sql in SQLite or SqlCE with C#
Is there anyway to batch two different select queries to either SQLite or SQLCE and get the two result sets back in C#? If not, is there an embedded database that handles this? ...
sql
which sql consumes less memory
Yesterday I asked a question on how to re-write sql to do selects and inserts in batches. I needed to do this to try and consume less virtual memory, since I need to move millions of rows here. The object is to move rows from Table B into Table A. Here are the ways I can think of doing this: SQL #1) INSERT INTO A (x, y, z) SELEC...
Help to translate SQL query to Relational Algebra
Hi everyone, I'm having some difficulties with translating some queries to Relational Algebra. I've a great book about Database Design and here is a chapter about Relational Algebra but I still seem to have some trouble creating the right one: Thoes queries I've most difficuelt with is these: SELECT COUNT( cs.student_id ) AS counter F...
SQL: Getting the full record with the highest count.
I'm trying to write sql that produces the desired result from the data below. data: IDNum Opt1 Opt2 Opt3 Count 1 A A E 1 1 A B J 4 2 A A E 9 3 B A F 1 3 B C K 14 4 A A M 3 5 B D G 5 6 C C E 13 6 C C M ...
How can I alter a temp table?
I need to create a temp table, than add a new int NOT NULL AUTO_INCREMENT field to it so I can use the new field as a row number. Whats wrong with my query? SELECT post, newid FROM ((SELECT post`test_posts`) temp ALTER TABLE temp ADD COLUMN newid int NOT NULL AUTO_INCREMENT) edit: SELECT post, newid FROM ((SELECT post, newid as int ...
Sql query Error: Operand should contain 1 column(s) in rails
Hi everyone, SELECT * FROM `jobs` WHERE (SELECT DISTINCT jobs.* FROM jobs, job_requests WHERE (jobs.user_id = 1) OR (job_requests.user_id = 1 AND job_requests.job_id = jobs.id) ) This sql gives me: Mysql::Error: Operand should contain 1 column(s). If I execute the select from the wh...
Why is OracleDataAdapter.Fill() Very Slow?
I am using a pretty complex query to retrieve some data out of one of our billing databases. I'm running in to an issue where the query seems to complete fairly quickly when executed with SQL Developer, but does not seem to ever finish when using the OracleDataAdapter.Fill() method. I'm only trying to read about 1000 rows, and the quer...
php while loop throwing a error over a $var < 10 statement
Okay, so for some reason this is giving me a error as seen here: http://prime.programming-designs.com/test_forum/viewboard.php?board=0 However if I take away the '&& $cur_row < 10' it works fine. Why is the '&& $cur_row < 10' causing me a problem? $sql_result = mysql_query("SELECT post, name, trip, Thread FROM (SELECT MIN(ID) AS min_i...
LINQ-to-SQL IN/Contains() for Nullable<T>
I want to generate this SQL statement in LINQ: select * from Foo where Value in ( 1, 2, 3 ) The tricky bit seems to be that Value is a column that allows nulls. The equivalent LINQ code would seem to be: IEnumerable<Foo> foos = MyDataContext.Foos; IEnumerable<int> values = GetMyValues(); var myFoos = from foo in foos wh...
retrieve comma delimited data from a field in using SQL
I've created a form in PHP that collects basic information. I have a list box that allows multiple items selected (i.e. housing, rent, food, water). If multiple items are selected they are stored in a field called Needs separated by a comma. I have created a report ordered by the persons needs. The people who only have one need are...
SQL Joining on a one-to-many relationship
Ok, here was my original question; Table one contains ID|Name 1 Mary 2 John Table two contains ID|Color 1 Red 1 Blue 2 Blue 2 Green 2 Black I want to end up with is ID|Name|Red|Blue|Green|Black 1 Mary Y Y 2 John Y Y Y It seems that because there are 11 unique values for color and 100...
OrderBy and Distinct using LINQ-to-Entities
Here is my LINQ query: (from o in entities.MyTable orderby o.MyColumn select o.MyColumn).Distinct(); Here is the result: {"a", "c", "b", "d"} Here is the generated SQL: SELECT [Distinct1].[MyColumn] AS [MyColumn] FROM ( SELECT DISTINCT [Extent1].[MyColumn] AS [MyColumn] FROM [dbo].[MyTable] AS [Extent1] ) AS [Distinct1]...
MySql scoping problem with correlated subqueries
Hi, I'm having this Mysql query, It works: SELECT nom ,prenom ,(SELECT GROUP_CONCAT(category_en) FROM (SELECT DISTINCT category_en FROM categories c WHERE id IN (SELECT DISTINCT category_id FROM m3allems_to_categories m2c WHERE m3allem_id = 37) ) cS ) categories ,(SELECT GRO...
How do I insert into a unique key into a table?
I want to insert data into a table where I don't know the next unique key that I need. I'm not sure how to format my INSERT query so that the value of the Key field is 1 greater than the maximum value for the key in the table. I know this is a hack, but I'm just running a quick test against a database and need to make sure I always sen...
Need Help with SQL Subquery
Hey: I am trying to write a query that will return all orders that only have a Subscription included. It is easy enough to write a query that includes all Orders with Subscriptions, another that includes all orders without a Subscription and then compare them with an unmatched query. But I don't want to have to store Queries in my Acce...
How can I compare rows from 2 tables that have composite primary keys?
Here's the scenario: I have 2 tables with data, one is the 2009 version and the other is the 2010 version. The primary key for each of the tables is a composite key. I know there is a different number of rows in each one and I need to find out the differences. Typically, in the "normal" primary key set-up, I would just look for primary...
Newbie SQL joining question: houses, refrigerators, and apples
There are three tables in my database: apples, refrigerators, and houses. Each apple belongs to a refrigerator (it has a column that references the refrigerator that it's in), and each refrigerator belongs to a house. The problem is, each apple ALSO belongs to a house. I guess this isn't "normalized" or whatever, but it's the way it is. ...
Is it a Good Practice to Add two Conditions when using a JOIN keyword?
I'd like to know if having to conditionals when using a JOIN keyword is a good practice. I'm trying to filter this resultset by date but I'm unable to get all the branches listed even if there's no expense or income for a date using a WHERE clause. Is there a better way of doing this, if so how? SELECT Branches.Name ,SUM(Expenses....
With a SELECT...WHERE id IN (...), order results by IN() ?
Possible Duplicate: Ordering by the order of values in a SQL IN() clause With a query such as: SELECT * FROM images WHERE id IN (12,9,15,3,1) is it possible to order the results by the contents of the IN clause? The result I'm looking for would be something like: [0] => Array ( [id] => 12 [file_name] =...
making a combined sum of two columns
I have a table (apples) containing: cid date_am date_pm ---------------------- 1 1 1 2 2 1 3 1 3 1 1 2 I asked a question earlier (badly) about how I would rank the customers in order of the number of ones(1) they had. The solution was (based on one column): SELECT cid, sum( date_pm ) A...