sql

Regex for non ANSI-style joins?

One of our rules is that all database joins have to be ANSI-style. As part of our build process I would like to scan all joins committed to source control for violations. I got a partial answer which I will post below but I'm sure its missing something and there must be a better one. Here is a non-comprehensive list of examples Shoul...

SQL join that displays left table even if right table doesn't match where

I have 2 tables, defined as such: CREATE TABLE `product` ( `pid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR( 50 ) NOT NULL, `description` TEXT, `qty` SMALLINT( 5 ) UNSIGNED NOT NULL DEFAULT '0', `category` ENUM( '1', '2', '3', '4', '5', '6', '7', '8' ) NOT NULL DEFAULT '1', `price` DECIMAL( 7, 2 ) UNSIGNE...

Updating a null cell - this stored procedure isn't working - why?

im doing a gym software. i have a signin signout system.when members sign in this data is put in a table leaving the signout colomn empty.then when member sign out the signout cell is updated.up to here is fine. but if the member signed in and out twice i dont want the sigout time of the first time to be changed.i want only the null cell...

Why doesn’t Client-initiated Ado.Net transaction allow…?

Hi If we’re using client-initiated Ado.Net transactions, then when we try to execute a command that is not a part of current transaction while the transaction is underway, we’ll receive an error. Why is that? thanx ...

SQL Substring using two charindexes problem

I have the following query: DECLARE @str VARCHAR(500) SET @str = 'Barcode: 22037 CaseSize: 1 Qty: 3' SELECT RTRIM(LTRIM( SUBSTRING(@str, CharIndex('CaseSize: ', @str) + 10, CHARINDEX('Qty:', @str)) )) The following results in: '1 Qty: 3' I would like to be able to only select the Case Size number, which is one in t...

How do I add a foreign key to an existing sqlite (3.6.21) table?

I have the following table: CREATE TABLE child( id INTEGER PRIMARY KEY, parent_id INTEGER, description TEXT); How do I add a foreign key constraint on parent_id? Assume foreign keys are enabled. Most examples assume you're creating the table - I'd like to add the constraint to an existing one. ...

Putting a condition on a many-to-many query with ActiveRecord

I've got the following relationships in my model: class Show < ActiveRecord::Base has_many :service_shows has_many :services, :through => :service_shows end class Service < ActiveRecord::Base has_many :service_shows has_many :shows, :through => :service_shows end class ServiceShow < ActiveRecord::Base belongs_to :show belo...

MySQL View: How do I set default value of a calculated field to 0?

I use the following statement to create a percentage calculation in a view: round(((Surveys.RejectedAsAdvertising / Surveys.Responses) * 100),0) AS PercentageAdvertising Basically, I divide the number of responses to a certain question (in this case, is something considered to be advertising) by the total number of responses. In the e...

Delete data from all tables in MYSQL

I have 100 tables, 40,000 rows in each table. I want to go into MySQL and delete all rows from all tables. ...in 1 statement, if possible? I want to keep the database and tables. ...

In TSQL, what is the best way to iterate through 1..* child nodes of XML data and retreive values?

I have a simple XML structure: <Receipt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ReceiptID="0" UserID="0" UserCardID="0" PaymentMethodID="1" MerchantID="0" MerchantTaxID="MERCHANT_TAX_ID" MerchantReceiptID="MERCHANT_RECEIPT_ID" MerchantReceiptReferenceID="MERCHANT_RECEIPT_REF_I...

How to get aggregates without using a nested sql query

I am writing a custom report from an Avamar (Postgresql) database which contains backup job history. My task is to display jobs that failed last night (based on status_code), and include that client's success ratio (jobs succeeded/total jobs run) over the past 30 days on the same line. So the overall select just picks up clients that f...

TSQL in SQL 2005: Query

Hi there I have 3 tables: Customer, CustomerTypes, CustomerCustomerTypes. CustomerCustomerTypes is basically is a bridge table between the Customer and CustomerTypes. Table structure: Customers: CustomerID CustomerName CustomerTypes: CustomerTypeID CusctomerTypeName CustomerCustomerTypeID CustomerID CustomerTypeID Sample Data: Custo...

What's the difference between VARCHAR and CHAR?

What's the difference between VARCHAR and CHAR in MySQL? I am trying to store MD5 hashes. ...

How to write an and operation in a SQL statement?

mysql_query("insert into mytable (flow,holderid,amount,operator,ip,product,taskid,comment)values('-1','$memberid','$sum+5','$memberid','$ip','Expertise','$taskid','Publish a problem or task') ")or die(mysql_error()); I get an error about '$sum+5' MySQL doesn't treat it as an and operation, how to resolve this problem? ...

SQL Column to row conversion

I have a table with the following structure: Col1 Col2 --------------- 1 2 3 4 I need to Query in the such a way that the output should be like: ColumnName | ColumnValue ---------------------------- Col1 1 Col2 2 Col1 3 Col2 4 Any help in this will be greatly appreciated. Th...

SQL : Tricky If Exists query needed to check overlapped values

I am having a table in SQL server which stores MINIMUM_AMOUNT,MAXIMUM_AMOUNT and CURRENCY_ID Now i want to frame an SQL query which will check the new values to be inserted are already existing in the table. Ex : My table is having 2 records as follows RANGE_ID MINIMUM_AMOUNT MAXIMUM_AMOUNT CURRENCY_ID -----------------------...

Update a table with records from another table

I am trying to update my table 1 with some field value from table 2 based on a condition. But am unable to get the proper query. Here's the condition: I have a table 1 with date field which should be updated from table 2 date field. The condition is that the id of table 1 should be equal to id of table 2 (Table 2 has id of table 1 as FK...

How to insert xml into a node in another xml using XQuery?

Hi all, I have two xml variable say @res, @student in a stored proc in SQL server 2005. @res contains <Subject>English</Subject> <Marks>67</Marks> <Subject>Science</Subject> <Marks>75</Marks> @student contains: <Student> <Name>XYZ</Name> <Roll>15</Roll> <Result /> <Attendance>50</Attendance> </Student> I need to in...

select statement for session

Hi,I am new to .net, i write the select statement for session as like Session["RoleId"] = "select roleid from AdminLogin where username='" + txtUserName.Text + "'"; but i m getting error what is problem with this. Thanks in advance... ...

SQL: are multiple commands separated with ';' atomic or not?

Hello. Is it any information about commands separated with ';' inside one query - are they atomic or not? I'm interested in actual versions of popular databases: MySQL, MSSQL, SQLite etc? For example, if 100 clients will spam following query: "insert into test ( name ) values ( '1' ); insert into test ( name ) values ( '2' )" Will da...