hello, I have a similar question to this one http://stackoverflow.com/questions/501347/sql-products-productsales I want to do that same query but instead of just checking quantity i want to check "total" (ie. quantity * price). "price" is a field in the sales table. here is the original query suggested on that link: SELECT p.[name] ...
Hi, Im very new to SQL but need to write a query to do the following. Using MS SQL Server 2005. Profile DefinitioninProfile Definition ------ ------------------- ---------- ProfileID DefinitionID DefinitionID ProfileType ProfileID ...
I have a case where i need to translate (lookup) several values from the same table. First way I wrote it, was using subqueries: SELECT (SELECT id FROM user WHERE user_pk = created_by) AS creator, (SELECT id FROM user WHERE user_pk = updated_by) AS updater, (SELECT id FROM user WHERE user_pk = owned_by) AS owner, [name] FRO...
How to write one SQL query that selects a column from a table but returns two columns where the additional one contains an index of the row (a new one, starting with 1 to n). It must be without using functions that do that (like row_number()). Any ideas? Edit: it must be a one-select query ...
When I use the query designers in VS2008, how do I save the results to a file? The output window is truncating the XML datatype - even if it didn't truncate, it still requires cut and paste to save the results to a file. ...
I need to create a background job that processes a table looking for rows matching on a particular id with different statuses. It will store the row data in a string to compare the data against a row with a matching id. I know the syntax to get the row data but i have never tried comparing 2 rows from the same table before? How is it do...
Hi, I have this SQL Job (in SQL Server 2005) that creates a backup every six(6) hours, the backup's filename is based on the timestamp so that it will create a unique filename(dbname_yyyymmddhhmmss.bak), Now my question is, How would I know using xp_cmdshell if the file is three day old and based on my script I want to delete backup(.bak...
I am using the following query to get the transactions from a table made to and from a user. I then want to retrieve the username for the sender_id and for the recipient_id. However I can only seem to get it for the recipient_id or the sender_id. Anyone have any ideas how I can get both. SELECT us.name, ta.amount, ta.recipient_id, ta.se...
I want to create a one off backup of a database that I can then restore on my local machine. I do not want to have any impact on the current media set. I see an option "Back up to new media set, and erase all existing backup sets". What I really want is "Bak up to new media set". ...
I am trying to archive some of my tables into another database on the same server. However the INSERT INTO...SELECT...FROM gives me an error (SQLSTATE=42704) on build. The table exists in the second database. Can anyone help with this? ...
I have a database of components. Each component is of a specific type. That means there is a many-to-one relationship between a component and a type. When I delete a type, I would like to delete all the components which has a foreign key of that type. But if I'm not mistaken, cascade delete will delete the type when the component is dele...
Specifically, Sql Server 2005/T-Sql. I have a field that is mostly a series of two characters, and they're all supposed to be upper case but there's some legacy data that predates the current DB/System, and I need to figure out which records are in violation of the upper casing covenant. I thought this would work: select * from tbl wh...
Hello, I have the 3 following tables in a MySQL 4.x DB : hosts: (300.000 records) id (UNSIGNED INT) PRIMARY KEY name (VARCHAR 100) paths: (6.000.000 records) id (UNSIGNED INT) PRIMARY KEY name (VARCHAR 100) urls: (7.000.000 records) host (UNSIGNED INT) PRIMARY KEY <--- links to hosts.id path (UNSIGNED INT) PRIMARY KEY <--- links ...
I have SQL SELECT statement that returns: Error: ORA-00600: internal error code, arguments: [qerpfAllocateR], [], [], [], [], [], [], [] If I narrow my results by adding one more condition in WHERE clause everything is ok. Anyone knows what is happening? EDIT: select * from ( select tbl1.col1, ..., tbl1.points from tabl...
I'm working with an Access database and I need to concatenate different related rows into 1 row. I found a solution here and used it with great success. However I need to add extra conditions to it, it should only be done if certain other columns are equal too. For example: 1 X Alpha 2 Y Beta 1 X Gamma 1 Z Delta should become 1 X A...
Hi, I am trying to perform a simple calculation to some data returned from SQL Server: SELECT val1X, val1Y, val2X, val2Y FROM myTable I want to be able to calculate the following against the returned values and return just a single value - the result of the calculation below (this was originally written in VB6): If IsNull(val1X) O...
I'm building a MySQL query to determine how many items from each of several categories appear in a given date range. My initial attempt looked like this: select Title, (select count(*) from entries where CategoryID=1 and Date >= @StartDate and Date <= @EndDate) as Cat1, (select count(*) from entries where CategoryID=2 and Da...
This is a follow up to this question. Here is my schema CREATE TABLE A( id serial NOT NULL, date timestamp without time zone, type text, sub_type text, filename text, filepath text, filesize integer, lock_status int ); In this database, a user can update the type,sub-type,filename,filepath,fil...
Is there any way of tracing user's identity when he/she triggers a a delete trigger on T-SQL 2005, e.g. with UNIQUEIDENTIFIER? If I do record the UNIQUEIDENTIFIER, how do I trace it back to the user? ...
I recently imported about 60k records into a table that relates data in one table to data in another table. However, my client has since requested that a sort order be added to all 60k records. My hope is there is a nice clean way to auto generate these sort orders in a SQL Update. The finished data should look something like this: i...