sql

Recursive query problem

i have a table which contains the following fields •Supervisorid •Empid This is just like a referral program. A guy can refer 3 guys under him i.e, 3 is referring three guys namely 4 5 8 similarly 4 is referring 9 10 and 11 likewise 8 is referring 12, 13 it goes like this.. I want a query to get all EmpId under Supervisor 3 ...

help me SELECT-ing from chained stored procedures

I added my local server pblack as linked server in SQL Server 2008 R2 ---1) EXEC master.dbo.sp_addlinkedserver @server = N'pblack', --'pblack' is my localhost @srvproduct=N'SQL Server' and executed successfully 2) and 3): --2) sp_MSforeachtable @command1="EXEC sp_spaceused '?'" --3) SELECT * INTO #te...

Fastest way to fetch a subset (200M) from a very large table (600M) in SQL Server

Hi, we are facing the following problem and we are trying to come up with the best possible solution. We are using SQL Server 2008. We have a table that has more than 600 millions records, and has about 25 columns. One of the columns is an ID and is indexed. We need to get a subset of records from this table. There are mainly 2 cases: ...

Recursive query

I have a table which contains the following fields Supervisorid Empid This is just like a referral program. A guy can refer 3 guys under him i.e, 3 is referring three guys namely 4 5 8 similarly 4 is referring 9 10 and 11 likewise 8 is referring 12, 13 it goes like this.. I want a query to get all EmpId under Supervisor 3 ...

Design patterns to ease migration of SQL code between vendors

I am looking at ways which a developer can take advantage of implementation specific advanced features(T-SQL, PL-SQL etc) and at the same time makes it easy to migrate the ANSI compliant SQL code to a new database. By that I mean, how to efficiently abstract the ANSI compliant SQL code and move it across different databases? ...

CakePHP: add variables to query in controller

Hi, I have a controller action that uses a sql query: $tag = $this->params['tag']; $this->set('projects', $this->Project->query('SELECT * FROM projects INNER JOIN projects_tags ON projects.id = projects_tags.project_id INNER JOIN tags on projects_tags.tag_id = tags.id WHERE tags.tag LIKE $tag')); As you can see at the end I ...

SQL: dealing with unique values in the table when UNIQUE key constraint isn't applicable

What you do when you need to maintain a table with unique values when you can't use UNIQUE constraint? For example, I use MySQL and want to map my urls to ids. So I create a table: CREATE TABLE url (id INTEGER PRIMARY KEY AUTO_INCREMENT, url VARCHAR(2048)); The problem is that mysql doesn't allow unique field bigger than 1000 bytes. ...

Host WPF Application online with Databinding

Hi All, 3 months ago i developed a WPF Application offline,and now i need to host this Application online. This app gets the function to Add,Edit,Delete,Search (and so on ...) records in the database so it use a Databinding and now i need to host it online so the client can connect on the web to insert or update the data . Can you sugges...

SQL Update column value of row 1 from value of row 2

Hi all We are using SQL 2005 (MS), I have a table that stores transactions. each time a specific event occurs 3 rows are added to for a specific transaction to that table. Now we want to change (update) a column in row 1 called commision which in row 1 is null with the value of the same column from row 2 which has a value say 123. is ...

sql query to find fifth record

How can i find the fifth record in a table using sql query? ...

Android database - Cursor cause crash.

Hi! Im trying to program with database in Android. So far i have made the database and written to it. The problem: When i try to read the data back from the database the program "stoped unexpectedly". I have tested to find what cause the crash and has found that it's this line: Cursor c = db.getAllTitles(); But i dont understand why ...

Group by problem

I need to group by ,even if is there a difference in values for a column by converting to a text value and needs that text value to be included in select list How is this possibe? SELECT col1,col2 FROM ( SELECT col1,col2 FROM table1 UNION ALL SELECT col1,col2 FROM table2 UNION ALL SE...

SQL Server Query is faster in SSMS when including execution plan.

In SQL Server 2005 management studio I am testing a query which uses some table variables, one of which has a clustered unique constraint on it. I am noticing that the total execution time actually goes down by a good amount when I include the actual execution plan to analyze it. What is the reason for this, and should I only test f...

How to divide two columns?

Hi, I tried to divide two columns from joined tables but the result (value of column relative_duration) is always 0. The query is the following: SELECT t1.[user_1] ,t1.[user_2] ,t1.[total_duration] ,(t1.total_duration/t2.[total_events_duration]) AS relative_duration FROM [CDRs].[dbo].[aggregate_monthly_events] A...

Comparing SQL date without current month

Hi! I have a table in Access with string columns and a date column. I want to get all the rows from the table when the date is lower than 22.10.2010, except this month. So, i need the rows from 30.09.2010 to ... I tied something, but I figured out it's not right: SELECT name FROM table WHERE YEAR(date)<=2010 AND MONTH(date)<10 But ...

How to get SQL Server schema changes especially "column name" changes

I am using trigger to trace DDL changes but when I change column name from the SQL Server Management Studio the trigger is not working? create TRIGGER trgLogDDLEvent ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS FOR AS DECLARE @data XML SET @data = EVENTDATA() IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') <> 'CREATE_ST...

please help with statistics problem

Hi All, Let's say I have a contact manager system. There are notes associated to each contact made by employees. So, here's my quick example: ContactName, NoteCount John, 100 Rob, 10 Amy, 10 Chris, 10 How do i figure out the that 75% of contacts have 10 notes assoicated with them and that 25% of contacts have 100 notes associated w...

SQL Slow Spatial Data

I have two tables one called Point and other called Poly i have around 400 polygons in the Poly table and around 200 thousand points in Point table. what i wanted is to find how many points are there in a certain polygon. The query i am using is: select COUNT(*) from point p inner join Poly pl on pl.GeoDataID = 101 and p.geopoint.STInt...

Shared Stored procedures for Multiple Databases

We have an application that has multiple identical databases apart from customer customizations. we would like to centralize the stored procedures that are common across all databases. Currently, we are having problems with keeping all databases up to date as customizations are done in the common database. We have tried to make some cus...

Return 0 if field is null in MySQL

I am trying to pull data from multiple tables to do accounting computation determining credit balance on customer accounts. If any of the total fields are NULL, I want MySQL to return 0 for those fields. Here is what I have: SELECT uo.order_id, uo.order_total, uo.order_status, (SELECT SUM(uop.price * uop.qty) FROM uc_order...