sql

Are return statements required for stored procedures?

If you have a stored procedure that deletes record in a table, do you have to put a return statement and why? I have always never put a return statement, but I just saw a snippet that has a return statement. Sample: DELETE FROM TableName WHERE TableId = @Id RETURN ...

Updating sql database problem php

Hey, I am trying to make a password retrieval system on my site, and I am having problems updating the password reset field in my database. I have tried everything, but nothing seems to work. This is my code so far: $passwordreset = md5(mt_rand()) . md5(mt_rand()) . md5(mt_rand()); $con = mysql_connect("localhost","XXX","XXX"); if...

Zend: How to use SQL query with 'like' keyword?

I am using zend framework. I am using following query in zend and it is working for me perfectly. $table = $this->getDbTable(); $select = $table->select(); $select->where('name = ?', 'UserName'); $rows = $table->fetchAll($select); Now I want to create another query in zend with 'like' keyword. In simple SQL it is like that. SELECT * ...

Using Database storage for uses other than offline apps

I've been looking into offline database storage with HTML5 and the built in Sqlite Db. I think it has a potential for things other than just storing data for offline apps. What I'm interested in, is hearing about potential uses of the database for storing things other than offline web page content. What has anyone in the commnity been u...

How do I bring back an entire range of dates in SQL between two dates, even when there is no data?

Trying to get the number of incidents in my database, in the format: date numberOfIncidents date numberOfIncidents date numberOfIncidents date numberOfIncidents I supply the start date / end date to my SQL stored procedure, but if there is no entry in the database for a particular date (because no incidents occured) then I won...

How to update and select a row within one query in SqLite?

I want to update and select one row within one query in SqLite. In MySql my wanted query would look like this: SET @update_id := -1; UPDATE data SET `Status` = 'running', Id = (SELECT @update_id := Id) WHERE `Status` = 'scheduled' LIMIT 1; SELECT * FROM data WHERE id=@update_id;" The above query will set the Status to 'running' and...

SQL Pivot Table

I have a table like this; var1 var2 var3 cats a b b cat1 b b cat1 a cat2 a a a cat3 a a a cat2 and want to get pivot table. I want to count a and b with the category of vars and cats.I am an excel user. I've looked other samples but could not solve th...

SQL Select Distinct with ints and varchars

I have a query which returns a number of ints and varchars. I want the result Distinct but by only one of the ints. SELECT DISTINCT t.TaskID , td.[TestSteps] FROM SOX_Task t snip Is there a simple way to do this? ...

Passing array as Parameter to SQL 2005 stored procedure

Hi All, How to pass array as parameter to a Store Procedure in SQL Server 2005, i this is not possible SQL server 2000, so we pass comma separated values in past. any help appreciated ...

Optimizing T-SQL query which constracts the same subtable twice

I want to optimize this query WITH CTE AS ( SELECT KladrItemName _KladrItemName , WordPositionKladrItem _WordPositionKladrItem , WordPositionAddressString _WordPositionAddressString , StartPosition _StartPosition , EndPosition _EndPosition , Metric _Metric , IsConstruction ...

using group by or use other function

we all know that using a group by we can eliminate extra column but then what if i wanted to group item A together. objects related to item A will be shown under the heading item A. but when i use a group by, it would only show one line of object under item A. please dont ask me use count. i just need to show all the objects related unde...

Convert pair comparison sql for MS SQL in Hibernate

We use hibernate to handle different databases. There's a problem about using pair comparison sql when the data source is MS SQL. We want to know how to convert this statement for ms sql. The following is the simplified sql. notice: column1 and column2 both are uuid and numeric type. ( in ms sql will be numeric(19,0) the converted s...

deleting multiple records from two tables

I have two tables called TableA and TableB. TableA has the following fields: TableA_ID FileName TableB has the following fields: TableB_ID TableA_ID CreationDate There is a foreign key link between the two tables on the TableA_ID field I need to delete records from both tables. I need to look at the “CreationDate” on TableB and ...

FULL OUTER JOIN with sqlite

sqlite only have INNER and LEFT JOIN. Is there a way to do a FULL OUTER JOIN with sqlite? ...

mysql query, better way to write this?

EDIT I'm understand the table is a mess. I took over this project and am rewriting the PHP and making serious changes to the database. I am not asking how I should layout the database. I need to make a quick, temporary fix and am looking for a better way to write the query below. END EDIT Hi there! So I have this query(below) this is ...

Use SOUNDEX() word by word on SQL Server

Hi there, Here is my problem. For example I have a table Products that contain a field Name: Products ID | Name | .. 1 | "USB Key 10Go" 2 | "Intel computer" 3 | "12 inches laptop computer" ... I'm currently implementing a simple search engine (SQL Server and ASP .NET, C#) for an iPhone web-app and I would like to use the SOUNDEX()...

SQL insert statement

I want to do the following: I have a table called Name which has an ID field. I have another blank table called Transcript I want to take the ID#s from Name and insert them into Transcript where they do not exist. Secondly I want to create 10 records with a different COURSE# value in the Transcript table. Therefore for each Name.ID ...

SQL Query to select bottom 2 from each category

In Mysql, I want to select the bottom 2 items from each category Category Value 1 1.3 1 4.8 1 3.7 1 1.6 2 9.5 2 9.9 2 9.2 2 10.3 3 4 3 8 3 16 Giving me: Category Value 1 1.3 1 1.6 2 9.5 2 9.2 3 4 3 8 Before I migra...

Create table and fill with test data provided as text

What is your way of creating and populating test tables in MySQL when looking into SO questions where tables (but not CREATEs) and test data (but not INSERTs) are provided? And what client do you use? EDIT: Here's a simply example of what I mean on SO. ...

php insert sql function

Hello everybody, I want to create one insert function in php, which I will use to insert data into database. Of course all inserts are not the same, some use one table others use other, tables have different column numbers etc. What is the best way to do this ? I'm currently on the web page for member registration, now I don't want to ...