sql

Select rows from query with a distinct foreign key?

I am having trouble just now with yet another SQL problem. I really need to take some time out to learn this properly. Anyway I have this query that someone else wrote and it gets values from a few different tables. Now more than one item can have the same ProductID. So there may be 3 items returned all with the same ProductID but they...

Outer join or dynamic query, which is the best way to go?

I have the following tables (I've simplified the data contained in the tables). RateTable - rate_table_id [int] - rate_table_name [nvarchar(50)] RateTableUsed - rate_used_id [int] - rate_table_id [int] (key to RateTable.rate_table_id) - customer_id [int] RateTableExtra - rate_table_extra_id [int] - rate_ extra_id [int] (key to...

Insert results of subquery into table with a constant

The outline of the tables in question are as follows: I have a table, lets call it join, that has two columns, both foreign keys to other tables. Let's call the two columns userid and buildingid so join looks like +--------------+ | join | |--------------| |userid | |buildingid | +--------------+ I basically need to...

Why is this SQL statement very slow?

Hi, I've got a table with about 1 million records (running SQL Server 2008 Web). I've got a search routine which tries to match on product code as well as product description. However in some circumstances it's very slow. Below is (cut-down) sql statement: WITH AllProducts AS ( SELECT p.*, Row_Number() OVER (ORDER BY ProductId)...

Updating MySQL with a where clause

I'm trying to update a field where username = $username UPDATE userinfo SET password = $newpass WHERE username = $username However, I'm getting the error "#1054 - Unknown column 'bob' in 'where clause'" when I replace $username with bob. Any idea how to correctly write this? ...

Grouping Consecutive "Wins" in a Row

Given rows: symbol_id profit date 1 100 2009-08-18 01:01:00 1 100 2009-08-18 01:01:01 2 80 2009-08-18 01:01:02 2 -10 2009-08-18 01:01:03 1 156 2009-08-18 01:01:04 2 98 2009-08-18 01:01:05 1 -56 2009-08-18 01:01:06 1 18 2009-08-18 01:01:07 3 ...

Is there a quick way to change the keys and foreign keys of my database during the development stage with script

Is there a quick script I can use in SQL Server to change the ID from int to unique identifier without physically going through and doing it manually. I have contemplated code gen to achieve this, but I would have thought using TSQL would be the quickest if possible that is! ...

Firebird UDF creation failure

I have a firebird database that I need to recreate. It contains an external UDF function. I made an SQL dump of the DB structure using IB Expert: DECLARE EXTERNAL FUNCTION LPAD CSTRING(255), INTEGER, CSTRING(1) RETURNS CSTRING(255) FREE_IT ENTRY_POINT 'IB_UDF_lpad' MODULE_NAME 'ib_udf' However, I get an error when I run th...

Suspended replication process, can I kill it?

Hello; On one of my servers that has a push transactional publication, I see the following session level information....as you can see below, this is a replication procedure (sp_replmonitorrefreshjob) and I'm hesitant to just kill the session because I'm not sure about whether this procedure is supposed to just hang around "waiting". Do...

sql help with getting data

I have a table called Forms with fields ID, FormName and a couple of other fields. I have another table called FormFields which has the FormId as a foreign key as well as an ID and a FieldName and FieldValue fields. The idea is that this should be able to save and retrieve data for generic forms. How can i get all the data from the Fo...

MySQL: MIN() and MAX() against a string value

I have an SQL SELECT statement joining 2 tables. The main table contains misc information on a product and is joined to a second table of sizes. The second table contains a list of non-numeric sizes stored as a string and is simply structured as follows... SizeID = Primary Key SizeName = String value of size (i.e. Small, Medium, Large) ...

using array in sql query

Hi, I have the following that grabs all the categories under a parent category: <?php $childrows = array(); $result2 = mysql_query("SELECT * FROM categories WHERE category_parent_id='$cid'"); while ($row = mysql_fetch_array($result2)){ $childrows [] = $row['category_id']; print_r($childrows); } ?> This returns: Array ( [0] => 47 ) A...

Begin and monitor progress on long running SQL queries via ajax

Is it possible to start a query that will take a significant amount of time and monitor progress from the UI via Ajax? I considered starting the process as a "run once" job that is scheduled to run immediately. I could store the results in a temporary table for quick retrieval once it's complete. I could also log the run time of the rep...

Constants in Oracle SQL query

I am new to Oracle (though familiar with SQL) and have to write a fairly complex query where a value derived from the current date is used many times. Rather than calculate the value each time, it would seem obvious to declare a constant for the purpose. However, when I then try to use my DateIndex constant in the subsequent SELECT sta...

SQL UPDATE with JOIN

I need to update table in SQL Server 2005 with data from its 'parent' table, see below: sale --------- id (int) udid (int) assid (int) ud --------- id (int) assid (int) sale.assid contains the correct value and want to update ud.assid with its value. What query will do this? I'm thinking a join but I'm not sure if its possible. ...

SQL server 2000 paging on a table with three keys

I've been trying to solve this problem for a few days now without much luck. I have found loads of resources that talk about paging on SQL Server 2000 both here and on codeproject. The problem I am facing is trying to implement some sort of paging mechanism on a table which has three keys which make up the primary key. Operator, Custome...

How to force Linq to update last edit time of a row?

In database, I have a LastEditTime column of type datetime. I used this to track last row update/insert time. Linq throws exception claiming that it can not insert null at that column. Naturally, since LastEditTime column is NOT NULL, an exception is expected. My hand generated query inserts getutcdate(). How can I ask Linq to do simila...

SQL to output line number in results of a query

I would like to generate a line number for each line in the results of a sql query. How is it possible to do that? Example: In the request select distinct client_name from deliveries I would like to add a column showing the line number I am working on sql server 2005. ...

SQL - WHERE clause Return results with next working day

Hi, I seem to be asking a lot of SQL questions at the moment. (I would normally write a program to sort the data into a report table, however at the moment that is not possible, and needs to be done using SQL) The Question I need a where clause that returns results with the next working day. i.e. Monday 01/01/01 the next working day wou...

SQL Alter Table then Modify Values

I have a SQL script that I am working on and I run into an issue when I'm creating (or editing) a column and then attempting to modify that new column. For example: BEGIN ALTER TABLE SampleTable ADD ColumnThree int END IF (EXISTS (SELECT * FROM sys.columns WHERE name = 'ColumnThree')) BEGIN UPDATE SampleTable SET ColumnThree ...