sql

Getting the first of a GROUP BY clause in SQL

I'm trying to implement single-column regionalization for a Rails application and I'm running into some major headaches with a complex SQL need. For this system, a region can be represented by a country code (e.g. us) a continent code that is uppercase (e.g. NA) or it can be NULL indicating the "default" information. I need to group thes...

Increment non unique field during SQL insert

I'm not sure how to word this cause I am a little confused at the moment, so bare with me while I attempt to explain, I have a table with the following fields: OrderLineID, OrderID, OrderLine, and a few other unimportant ones. OrderLineID is the primary key and is always unique(which isn't a problem), OrderID is a foreign key that isn...

Require help in Writing Query

The following image have been uploaded to show what I am trying to do and what I wanted out of it Can any one help me write the Query to get the results what I want Please check the following SELECT * FROM KPT WHERE PROPERTY_ID IN (SELECT PROPERTY_ID FROM khata_header WHERE DIV_ID...

SQL - Joining multiple records to one record

I've got a SQL Server database with the the following tables: Client (ClientID, ClientName) SalesAgent (AgentID, AgentName) Item (ItemID, Description) Purchase (PurchaseID, ClientID, ItemID, Price) PurchaseSalesAgent (PurchaseID, AgentID) Each purchase is only ever one item to one client but there can have been multiple agents invo...

Regular Expression library/set to parse sql

Anyone know of any re libraries out there to help parse columns, functions, and values from an SQL WHERE clause? Example strings: COLUMN=='VALUE' COLUMN=='VALUE' AND COLUMN2='VALUE' FUNCTION(COLUMN)==FUNCTION('VALUE') I'm working in Python, but can port most anything over... ...

CFQuery where param

In my CF component, I tried to filter data from user input (getSearchString) and run the code, I having problem with WHERE function. Can suggestion what is the correct way? <cffunction name="getParks" access="remote" returntype="struct"> <cfargument name="page" required="true" /> <cfargument name="pageSize" required="true" /> <cfargumen...

.NET | Persist multiple objects in minimum number of queries

I have a list of objects which needs to be persisted in a SQL Server database table, where each object gets persisted as single record. i.e. List of objects result in insertion of multiple records. Trivial way of saving the objects is to loop over the list and fire a query/stored procedure/etc. for saving that record. But this results i...

Using Excel VBA to Create SQL Tables

Hi All, I am trying to use Excel VBA to automate the creation of a SQL table in an existing SQL Database. I have come across the following code on this side. Private Sub CreateDatabaseFromExcel() Dim dbConnectStr As String Dim Catalog As Object Dim cnt As ADODB.Connection Dim dbPath As String Dim tblName As String 'Set databas...

Excel VBA SQL Import

Hi All, I have the following code which imports data from a spreadsheet to SQL directly from Excel VBA. The code works great. However I am wondering if somebody can help me modify the code to: 1) Check if data from column A already exists in the SQL Table 2) If exists, then only update rather than import as a new role 3) if does not ...

Query next/previous record

I'm trying to find a better way to get the next or previous record from a table. Let's say I have a blog or news table: CREATE TABLE news ( news_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, news_datestamp DATETIME NOT NULL, news_author VARCHAR(100) NOT NULL, news_title VARCHAR(100) NOT NULL, news_text MEDIUM...

Excel VBA to Create SQL Table

Hi All, I have found the following code which is suppose to create a SQL table within a SQl Database. The database is specify in dbpath and the table to be created is in tblname. However when i run this code i have a problem finding to right SQL database. For example if i specify the dbpath as "WIN2k8\Test\ABC" ie the machine name i...

Can in-memory SQLite databases scale with concurrency?

In order to prevent a SQLite in-memory database from being cleaned up, one must use the same connection to access the database. However, using the same connection causes SQLite to synchronize access to the database. Thus, if I have many threads performing reads against an in-memory database, it is slower on a multi-core machine than the ...

WinHost, Configuration Error, 'LocalSqlServer' wasn't found in the app config or connection string.

I've ran into a weird issue when ever I deploy to my winhost account and try to login using membership provider it works fine locally but once it's on the intarnets it breaks. Configuration Error Description: An error occurred during the processing of a configuration file require to service this request. Please review the specific erro...

Use SQL to filter the results of a stored procedure

I've looked at other questions on Stack Overflow related to this question, but none of them seemed to answer this question clearly. We have a system Stored Procedure called sp_who2 which returns a result set of information for all running processes on the server. I want to filter the data returned by the stored procedure; conceptually,...

Excel VBA SQL Data

Hi All, I have a small excel program. I would like to be able to use this program to update a SQL table. What would be the function to say update line 2 in SQL table Test in Database ABC Thanks ...

SQL Server ORDER BY/WHERE with nested select

I'm trying to get SQL Server to order by a column from a nested select. I know this isn't the best way of doing this but it needs to be done. I have two tables, Bookings and BookingItems. BookingItems contains StartDate and EndDate fields, and there can be multiple BookingItems on a Booking. I need to find the earliest startdate and lat...

Cursor while loop returning every value but the last

Hello, I am using a while loop to iterate through a cursor and then outputing the longitude and latitude values of every point within the database. For some reason it is not returning the last (or first depending on if I use Cursor.MoveToLast) set of longitude and latitude values in the cursor. Here is my code: public void loadTrack...

Querying Same Lookup Table With Multiple Columns

I'm a bit confused on this. I have a data table structured like this: Table: Data DataID Val 1 Value 1 2 Value 2 3 Value 3 4 Value 4 Then I have another table structured like this: Table: Table1 Col1 Col2 1 2 3 4 4 3 2 1 Both columns from Table1 point to the data in ...

Deleting rows from multiple tables in MySQL

Here is what i'm trying to do: Delete a project from the projects table and all the images associated with that project in the images table. Lets say $del_id = 10 DELETE FROM projects, images WHERE projects.p_id = '$del_id' AND images.p_id = '$del_id' What is wrong with this query? ...

Why does "non exists" SQL query work and "not in" doesn't

I spent some time trying to figure out why this query isn't pulling the results i expected: SELECT * FROM NGS WHERE ESPSSN NOT IN (SELECT SSN FROM CENSUS) finally i tried writing the query another way and this ended up getting the expected results: SELECT * FROM NGS n WHERE NOT EXISTS (SELECT * FROM CENSUS WHERE SSN = n.ESPSSN) The...