sql

Using a SubQuery in an Insert Statement in SQL Server 2005

I have a carsale project. It completely works on localhost. I have a "AddCar.aspx" page that inserts a car record with car's features. Car features are selected with checkboxes. If i don't check any checkbox, there is no problem. But if i check one of feature checkboxes, my page gives an error like this: "Subqueries are not allowed i...

Standard alternative to CONNECT BY?

I'm trying to convert some Oracle SQL queries to work with (in theory) any SQL database. Some of the queries are hierarchical in nature and are written using CONNECT BY. Is there a standard SQL alternative to Oracle's START WITH...CONNECT BY syntax? Or is there some recommended process I should follow to convert the hierarchical queries...

Basic clarifications about NHibernate

Given that I'm very good with SQL and c#, Should I learn another layer on top like NHibernate ? Is NHibernate just a library (that stores in a Database)? Or it's a service? Should I use NHibernate or ADO.NET Entity Framework? If you think I should learn/use an ORM, give me your top reason. ...

How do I know if record from an SQL database is being used elsewhere?

Is there a way to know that a record is being used by another record in the database? Using deleting as an example: When I create an SQL statement trying to delete a group in dbo.group I get this error: The DELETE statement conflicted with the REFERENCE constraint "FK_MyTable". The conflict occurred in database "MyDB", table "dbo.U...

SQL XML Bulk Loader - Error connecting

I'm using the sqlxmlbulkloadlib to perform an xml bulk load. When integrated security=SSPI, the process completes fine inside a workflow hosted on my local machine in a wcf service. However, when that workflow is hosted on our server and kicked off from a service reference on an aspx page, the bulkloader produces an error connecting to...

Groovy FindAll statement for finding values that don't exist.

Hi. I am trying to construct a Groovy statement to find values that don't exist in a pre-populated list. I'm using SQL and think I want to do something like : myList = [a, b, c, d, e ... lots more data] sql.findAll("SELECT * FROM table WHERE code not in " + <myList>) I have a feeling this is very simple .. I'm just not sure how...

Shema binding for a UDF using tables from other db

I have a UDF in SQL 2005 that I would need to schemabind as it is used in a view that I need an index on. This UDF gets information from a table that is located in a different db (same server) then the one where the UDF is located. Since it is invalid to specify table as [DBName].dbo.[Tablename], is there a way I can get the info from ...

Are subqueries the only option to reuse variables?

I'd like to use MySQL in this form: SELECT 1 AS one, one*2 AS two because it's shorter and sweeter than SELECT one*2 AS two FROM ( SELECT 1 AS one ) AS sub1 but the former doesn't seem to work because it expects one to be a column. Is there any easier way to accomplish this effect without subqueries? And no, SELECT 2 AS two is no...

SQL Pivot Table

I have a SQL view with following data: ID ClassName Description Flags 1 Class1 Desc1 F1 2 Class1 Desc1 F2 3 Class1 Desc1 F3 4 Class1 Desc1 F4 5 Class2 Desc2 F2 6 Class2 Desc2 F6 7 Class3 Desc3 F1 8 Clas...

SQL Update row by row

My SQL table has 2 fields that impact my problem: "Priority" as real, and Start_Date as datetime. As the user adds items to the table, (s)he specifies the Priority for a given Start_Date. If Item1 is Priority 1, then Item2 may be Priority 2 and it will be given a Start_Date after Item 1. But if Item2 is given Priority 0.5 (or any numbe...

sql query - some kind of select distinct?

Suppose I have a log of Customers who come in on particular Days, like: Cs day -- --- 01 Tue 02 Tue 03 Wed 01 Wed 04 Thu 02 Thu I need a query that returns only the #s of those Customers who were in both on Tue and on Wed. In this case, only Cs # 01. Thank you. ...

SQL Table Design - Identity Columns

SQL Server 2008 Database Question. I have 2 tables, for arguments sake called Customers and Users where a single Customer can have 1 to n Users. The Customers table generates a CustomerId which is a seeded identity with a +1 increment on it. What I'm after in the Users table is a compound key comprising the CustomerId and a sequence nu...

Set-theory way of determining a time difference between a "group" of records via SQL.

I have a log file in SqlServer that stores the time an application started, the time the application is ready (i.e. finished loading), and the time that it's exited. Each of these occur as a separate entry. The format (and sample data) is as follows: Date/Time User Type Application Message 2009-11-03 12:26:12.403...

Last Item In MySQL COUNT(*) Result?

Hello! Quick question... I have a query that checks for duplicates that looks like this: SELECT COUNT( * ) AS repetitions, Name, Phone, ID, categoryid, State FROM users GROUP BY Name, Phone, State HAVING repetitions > 1 ORDER BY ID DESC this works but MySQL returns the first ID in a set of duplicates. For example, lets say I h...

MySQL query for geographic midpoint

I need a MySQL query (or function) to calculate the geographic midpoint of an arbitrary number of latitude/longitude coordinates. I want to use Method C (Average latitude/longitude) as described on this page http://www.geomidpoint.com/calculation.html but can't figure out how to convert this into a SQL query. I'm looking for something of...

How do I do a GROUP BY and ORDER BY in PostgreSQL?

PostgreSQL is about to make me punch small animals. I'm doing the following SQL statement for MySQL to get a listing of city/state/countries that are unique. SELECT DISTINCT city , state , country FROM events WHERE (city > '') AND (number_id = 123) ORDER BY occured...

Trying to Count specific items in QUERY

Hi, I currently am returning a table that looks like this: Country Result ------ ----- Japan sunk Japan sunk USA ok USA ok with country and result being the column names Id like to know how many times japan and sunken show up? So the resulting query I am trying to achieve is: country numSunk ------- ----...

Can MySQL replace multiple characters?

I'm trying to replace a bunch of characters in a MySQL field. I know the REPLACE function but that only replaces one string at a time. I can't see any appropriate functions in the manual. Can I replace or delete multiple strings at once? For example I need to replace spaces with dashes and remove other punctuation. ...

Oracle Dynamic SQL for columns

I am writing a stored procedure for which I need to populate a table based on the data being reported on. In this situation, I will be pulling in three values per day for a certain code in a date range. Say on a certain run of this stored procedure, I have code values X, Y, and Z for a date range as so: select abc.code, abc.da...

Why won't MySQL use a reference index on the JOIN?

In the following example, MySQL fails to use to find a ref for the JOIN clause (or so it appears). Can anyone explain why? mysql> explain SELECT 1 FROM `businesses` INNER JOIN `categories` ON (`businesses`.`id` = `categories`.`business_id`) WHERE (`categories`.`category_id` IN (1321, 7304, 9189, 4736, 4737, 1322, 8554, 1323, 1324, 9...