sql

What are the Options for Storing Hierarchical Data in a Relational Database?

Good Overviews One more Nested Intervals vs. Adjacency List comparison: the best comparison of Adjacency List, Materialized Path, Nested Set and Nested Interval I've found. Models for hierarchical data: slides with good explanations of tradeoffs and example usage Representing hierarchies in MySQL: very good overview of Nested Set in p...

transfer function in cakephp see my explanation please

please help me with my simple cakephp application. i have a products_warehouses table: -- -- Table structure for table `products_warehouses` -- CREATE TABLE IF NOT EXISTS `products_warehouses` ( `id` int(5) NOT NULL auto_increment, `product_id` int(5) NOT NULL, `buyprice` decimal(8,2) NOT NULL, `sellprice` decimal(8,2) NOT NU...

SQL query: list all items in one table that do not appear in another table

I'm working on a training tracker program and I'm at a point where I cant figure out the SQL query. I have 3 tables: employees, trainingRecords, masterList. employees and trainingRecords are related through the empID fkey. trainingRecords and masterList are related through the TID fkey. Right now the training records table is bla...

How to force Hibernate to add quotes to generated SQL statements?

Hi! Hibernate is generating invalid SQL for a particular criteria query. I can manually fix the query by adding single quotes to the value being used in the WHERE clause. To fix it, I changed the query from: where (role0_.ROLE_ID=2L ) to: where (role0_.ROLE_ID=`2L` ) How to force hibernate to add single quotes (in mysql it is si...

sql query for counting rows with similar columns

HII Now i have a table with 4 columns as below seq col1 col2 col3 1 1 2 3 1 1 2 4 2 1 2 3 3 1 2 4 result should be as below number of seq1 s which have ( col1 col2 col3) same for the above example the output is expected as count(seq) col1 col2 col3 2...

SQL Joining the first, second and third row of joining table

I need to display some columns on the final result which comes from a join. Those columns comes in 3 groups, say: name_1, addr_1, prefecture_1, price_1, name_2, addr_2, prefecture_2, price_2, name_3, addr_3, prefecture_3, price_3 Where these 3 groups comes from a join which should return only 3 records according to a sort key carrier_...

Query speed up strategies

Hey all At the company i am working on we have an application build on Jboss/Apache/Hibernate with Ms Sql 2005 db. We have a page that loads a bunch of transactions. Now we timed this during loading of the page and it takes abnout 15-20 seconds to load the files, thsi is because the queries build (not sure if these are build by hibern...

SQL query to calculate value from two tables

Hi I have two tables which are related in the following way : Table1 gene goterms ape 1a ape 1b ape 1c boy 2a boy 1a cat 1a cat 1c Table2 gene gene count ape ape 3 ape boy 1 ape cat 2 boy ape 1 boy boy 2 boy cat 1 cat ape 2 cat boy 1 cat cat 2 now,i need value that corresponds to second row ...

Add on or tools for SQL Server

Like for .NET we have some special tools/framework/open source like Entity framework, log4net, various open source tools and projects.. Do we have similar kinds of add on's/tools/software for SQL Server [which is free, of course] ? I am a .NET developer and this is my first deep step to sql server, till now I have worked only on SP,View...

What SQL query for multiple rows with one value the same in each?

I am trying to insert an XML file of the following kind: <thing> <name>one</name> <type>metal</type> <type>round</type> </thing> <thing> <name>two</name> <type>round</type> </thing> <thing> <name>three</name> <type>metal</type> <type>round</type> </thing> into an SQL database. There are lots of <thing> ...

Resultset not open. Verify Autocommit is OFF. Apache Debry

Hi, I am using apache derby for my database. I am able to perform inserts into the database. The following is the excerpt from the code that attempts to display the contents of my only table 'MAINTAB'. The instance of java.sql.Connection is 'dbconn'. ResultSet word; Statement query; String getData="SELECT THEWORD FROM MAI...

SQL IN operator in the WHERE clause

The following query: SELECT SUM (peak_power) FROM t_mpi_pv WHERE info_id IN ( SELECT mp_info_id FROM t_mp WHERE mp_id IN ( ARRAY[29800001,29800008,29800015,29800022,29800029] ) ) Doesn't work. I get the error: "Operator does not exist: integer = integer[]". But SELECT SUM (peak_power) FROM t_mpi_pv WHERE info_id IN ( SEL...

sql query takes much long time compared to next run

Hi, I'm running a procedure which takes around 1 minute for the first time execution but for the next time it reduces to around 9-10 seconds. And after some time again it takes around 1 minute. My procedure is working with single table which is having 6 non clustered and 1 clustered indexes and unique id column is uniqueidentifier data...

SQL Pivot Problem - multiple aggregates?

I have the following data returned buy a simple SQL query. The Number of sites could change, but X,Y,Z are fixed (they are different types of accidents, and the data stored represents the number of occurances) | Site | X | Y | Z | -------------------- A 1 2 3 B 4 5 6 C 7 8 9 I need to get it to the ...

SQL Server Linked Database Aliases.

Is it possible to have not only a LinkedServer, but linked database and server? My situation is that of having one environment with a setup of: ServerX: DatabaseOne, DatabaseTwo and another environment of: ServerY: MyDatabaseOne, MyDatabaseTwo Now, DatabaseOne and MyDatabaseOne are exactly the same, as are DatabaseT...

Most efficent way to merge data into SQL Server database from ODBC source

I need to constantly merge (upsert/delete) data from an ODBC data source to a SQL Server 2008 database (number of rows vary from one row to 100000 of rows) What would you recommend as the most efficient approach (using .net 3.5 ): Use SqlBulkCopy into temp table then call stored procedure with Merge command using temp table as sour...

C# with INSERT Stored Procedure \r\n problem

Basically i've got a very simple insert statement which INSERT INTO [dbo].[ORDER] (ORDER_DATE ,ORDER_TYPE_ID ,PAYMENT_STATUS_ID ,TOTAL_COST ,SENDER_NAME ,SENDER_EMAIL ,SENDER_MESSAGE ,RECIPIENT_NAME ,RECIPIENT_ADDRESS) VALUES (@ORDER_DATE ,@ORDER_TYPE_ID ,@PAYMENT_STATUS_ID ,@TOTAL_COS...

Bulk insert using stored procedure

I have a query which is working fine: BULK INSERT ZIPCodes FROM 'e:\5-digit Commercial.csv' WITH ( FIRSTROW = 2 , FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) but now I want to create a stored procedure for it. I have written below code to make its stored procedure: create proc dbo.InsertZipCode @filepath varcha...

Running SQL 2008 Express in '2005' mode

Hi, currently i have SQL 2005 Express installed and wish to upgrade to 2008 express to gain the extra dbase limit (10gb) my question is, if i run 2008 express in 2005 mode will this trunkate the dbase to the 2005 limit of 4gb or will it remain at the 2008 limit of 10gb? Chris ...

Order By Columns

Does adding more columns to ORDER BY have a performance penalty? ORDER BY STYLE vs. ORDER BY STYLE, SIZE, COLOR ...