Programming SQL

Number of subordinates using NESTED SET
Hi all, I'm not an expert in SQL and I need a little help. I'm developing a php script working with a tree stored in database. Looking in Internet I found this article showing a good method for manage a tree using the nested set way: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html The ... 31 Mar 2010 11:01

Multiple Unions and functions
I have a function that has 4 unions. The queries are similar except for the where clauses It looks something like: SELECT... FROM... JOIN... WHERE @Arg1 = 0 and @Arg2 = 0 and e.EID IS NULL UNION SELECT... FROM... JOIN... WHERE @Arg1 = 0 and @Arg2 = 1 UNION SELECT... FROM... JOIN... WHERE @Arg1 = 1... 30 Mar 2010 21:52

Convert datetime field to interval
WOW!!! I ask for simple and that is what I get. This is very nice. THANK YOU!!! "jgurgul" wrote: Hi, DECLARE @DT DATETIME SET @DT ='03/29/2010 23:31:15' SELECT DATEADD(mi,-(DATEPART(mi,@DT) % 30),(DATEADD(mi, DATEDIFF(mi, 0, @DT), 0))) Jon "Kent Prokopy" wrote: Is ... 30 Mar 2010 12:47

joins and trusted FKs
Under what circumstances will the optimizer not include a table in the join if the FK is trusted? In this situation the INNER and LEFT both include the tbltrips table in the actual plan. SELECT tt.* FROM tbltripstemplate tt INNER JOIN tbltrips t on t.id = tt.tripid WHERE tt.id = 1 AND tt.tripdayofweek = 6 ... 31 Mar 2010 09:53

get records from before 6 months ago
Hello everyone, I'm trying to get records BEFORE 6 months ago based on 2 date feilds. Here's the script: if object_id('tempdb.dbo.#hha') > 0 drop table #hha select * into #hha from temp.dbo.op with (nolock) where (Date1 < DATEADD(MONTH, -6, GETDATE()) and Date1 <> '1900-01-01 00:00:00') or (Date2 < DA... 30 Mar 2010 13:54

SQL2008 > SQL2000
Hi Folks, Appologies if this is in the wrong group but I am at a client site and need to create a database which will be used on SQL2000, only problem is that they have lost the disk for the developer version of enterprise manager and as yet I dont have access to the target server. Soo. As I have SQL2008 d... 30 Mar 2010 21:52

A new approach to learning from Knowledge Horizon
Knowledge Horizon has launched a new and innovative approach for learning community. Through this innovation, you can reduce the learning time as well as improve your retention power. After a couple of years of research, we have come up two researched products for you. You can experience these innovation http... 30 Mar 2010 05:04

combine 2 selects in where clause
I have a Where clause that is doing 2 selects that I want to combine into one. The statement would be something like SELECT Name, Address FROM Work ws WHERE ( WS.SpaceID = (SELECT FixedSpaceID FROM Work_Defaults ... 30 Mar 2010 17:17

Building a string out of table columns values
I need to take the table variable @NonexistingHeadlines and build a string out of all of the values in the column HeadlineTitle. Is there a way to do it? ... 30 Mar 2010 10:33

Optimizing queries with isnull
If I had a query with columns surrounded by IsNull, would that slow the query down? For example if I had something.like: SELECT Phone= ISNULL(e.Phone, m.Phone), Name = ISNULL(e.Name,m.Name) FROM Employees e LEFT JOIN Managers m Would that have an affect on the query? Thanks, Tom ... 30 Mar 2010 22:59