sql

Parent - Child sql query

id parent_id 1 0 2 0 3 2 4 0 5 1 6 0 I need a query that will return parent rows (parent_id=0) followed by its child rows: first parent all children of first parent second parent all children of second parent third parent fourth parent Expected result: ordered by id id parent_id -------------------------------------...

Sql question: Getting Parent rows followed by child rows

id parent_id 1 0 2 0 3 2 4 0 5 1 6 0 I need a query that will return parent rows (parent_id=0) followed by its child rows first parent all children of first parent second parent all children of second parent third parent fourth parent Expected result: ordered by id id parent_id 1 0 (first par...

More tables or more databases?

Hello to all, I am setting up a system to host WordPress blogs for users of a site I run. Right now, things are functioning pretty well within one database and different blogs running with their own prepended tables (user1_posts, user_posts, etc). Despite this working so far, it feels a bit messy. If this database were to have 4000 t...

Exclude weekends and custom days (i.e. Holidays) from date calculations

Currently, I am calculating a finish date based on the start date (DateTime) and duration (# of days), but my calculations do not take into account weekends or holidays. So, my solution is not correct. This was just a starting point. I read some articles out there and one approach is to create a gigantic calendar table that has all the...

SQL LIKE Performance with only the wildcard (%) as a value

I am wondering what the performance of a query would be like using the LIKE keyword and the wildcard as the value compared to having no where clause at all. Consider a where clause such as "WHERE a LIKE '%'". This will match all possible values of the column 'a'. How does this compare to not having the where clause at all. The reason I...

sql to return unique list of UserID's based on 2 tables.

(not discussing the table design, its a done deal!) I know how to do this query using a WHERE NOT IN clause, but I was wondering how it could be done using a join. Note: rows are not unique, so we have to do a DISTINCT so as to not get duplicates. Users (userID) OldUsers(UserID) So using the WHERE NOT IN clause I can do: SELECT DIS...

how to use group by with union in t-sql

hi all, how can i using group by with union in t-sql? i want to group by the first column of a result of union, i wrote the following sql but it doesn't work. i just don't know how to reference the specified column (in this case is 1) of the union result. great thanks. SELECT * FROM ( SELECT a.id , a.time ...

a question about DATE_ADD

I want to add 1 month and 3 months, should I do like that: UPDATE `set` SET expire = DATE_ADD(`expire`, INTERVAL 1 MONTH) WHERE ID='$lid' UPDATE `set` SET expire = DATE_ADD(`expire`, INTERVAL 3 MONTHS) WHERE ID='$lid' is that right ? 3 MONTH or 3 MONTHS ? ...

Dealing with XML datatype(SQL SERVER 2005)

Hi, I have a table having 2 columns EmployeeId (int) and EmployeeDetails(XMl type) EmployeeId EmployeeDetails 1 <Employee><EmployeeDetails><EmployeeName> Priyanka </EmployeeName><Age> 24 </Age><Address> Argentina</Address></EmployeeDetails></Employee> 2 <Employee><EmployeeDetails><EmployeeName> Sarkar </EmployeeName><Age>...

How to implement posting limit of a user?

A user can post 4 times at most within a day. The difficulty is how to get the start of a day in database?I'm using MySQL ...

MySQL Select entries with timestamp after X time

I am attempting to populate a list of records within the last X seconds of the server time. My current query is.. mysql_query("SELECT player.name FROM player, spectate WHERE (player.pid = spectate.player) && spectate.bid=$bid"); This works currently to retrieve all entries. My first thought was to select the time field as well. And...

How to change column order in a table using sql query in sql server 2005?

Hi all, How to change column order in a table using sql query in sql server 2005? I want to rearrange column order in a table using sql query. Please help. ...

How to set the result of exec storedproc to a variable?

Hi all, I need to exec a storeproc and store its scalar result to a local variable inside a storedproc. How to implement? E.G. CREATE PROCEDURE [dbo].GetNthNo AS DECLARE @a INT DECLARE @d INT DECLARE @n INT DECLARE @S INT SET @S=EXEC spGetNthNo @a,@d,@n SELECT @S Please help. ...

Substitute MySQL result

I'm getting the following data from a MySQL database +----------------+------------+---------------------+----------+ | account_number | total_paid | doc_date | doc_type | +----------------+------------+---------------------+----------+ | 18 | 54.0700 | 2009-10-22 02:37:09 | IN | | ...

Aggregate function in comparison of 2 rows in the same table (SQL)

Given the table definition: create table mytable ( id integer, mydate datetime, myvalue integer ) I want to get the following answer by a single SQL query: id date_actual value_actual date_previous value_previous where: date_previous is the maximum of all the dates preceeding date_actual for each id and values corresp...

Sql Server 2005 Data Types

Hi, What is the diff between real, float, decimal and money. And most important, when would I use them. Like I understand - real and float are approx. types, meaning they dont store the exact value. Why would you ever want this? Thanks ...

.NET - SQL Select --> Array. What is the fastest way?

I'm using VB.NET. I am performing a select query that returns approximately 2500 rows, each containing 7 fields. I am using a SqlDataAdapater and filling a dataset with the single table returned by the Select query (from the local database). (I only perform the data-retrieval once (see below) and I don't even start the StopWatch until...

DataReader associated with this Command which must be closed first.

I am getting the following error; "There is already an open DataReader associated with this Command which must be closed first." is it because I have used the reader in foreach loop ? or what the problem might be ? Regards BK foreach( Apple a in listApple ) { .... using (SmartSqlReader reader = Db.CurrentDb.Execu...

Can I get all the fields in an item (in Sitecore)?

I'm trying to write a sql query to get all the fields in a given item in Sitecore. To say I am stuck is putting it mildly. I'm guessing I have to do some self joining on the fields table, but I'm getting myself in knots. Anyone have any ideas? ...

Counting Instances of Unique Value in Field

Suppose you have a table in SQL: Prices ------ 13.99 14.00 52.00 52.00 52.00 13.99 How would you count the amount of times a DIFFERENT field has been entered in? Therefore an example of such a count would output: 13.99 - 2 times. 14.00 - 1 times. 52.00 - 3 times. OR perhaps: 3 (i.e. 13.99, 14.00, 52.00) Can anyone advise? ...