sql

Insert rows of states and iso's from comma separated textfile into webbased Mysql

I'm building an international webshop and in the part where a customer has to fill in the address I wonder how I insert the rows in a comma separated text file with a list of stats in my web based MySQL? Example AM,04,"Geghark'unik'" AM,05,"Kotayk'" AM,06,"Lorri" AM,07,"Shirak" AM,08,"Syunik'" AM,09,"Tavush" AM,10,"Vayots' Dzor" I fou...

Postgresql MATCH PARTIAL work around?

I'm trying to work around Postgresql 8.4's lack of MATCH PARTIAL. I have the following schema: [vehicles] lot_id | vin | source | year | make | model ... primary key ( lot_id, vin, source ) [pictures] picture_id | lot_id | vin | url | sha1 ... primary key ( picture_id ) Now, what I want is a compound FOREIGN KEY that REFERENCES t...

passing list of name/value pairs to stored procedure

I have a name/value pair in a List<T> and needing to find the best way to pass these to a stored procedure. Id Name 1 abc 2 bbc 3 cnn .... ... What is the best way to accomplish this? ...

Is it possible to make query like: WHERE IF (q.id NOT IN (SELECT ... ?

Is it possible to make query like this? That has some error... Query should check if user did answer question with q.id. SELECT pictureid, id, points FROM questions q WHERE IF (q.id NOT IN (SELECT questions_id FROM history h WHERE h.users_id = 3)) ...

Order By a field being equal to a specific value?

Let's say I have this MySQL query: SELECT * FROM A WHERE x='abc' OR y=0; How can I prioritize the rows so that cases where x='abc' are ordered FIRST? If y=0 but x!='abc', I want those rows to come after cases where x='abc'. Can this be accomplished with a simple ORDER BY clause? Thanks! ...

SQL and ALL operator

Looking for an elegant way to workaround this... DECLARE @ZIP INT SET @ZIP = 55555 IF @ZIP = ALL(SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1) PRINT 'All people of type 1 have the same zip!' ELSE PRINT 'Not All people of type 1 have the same zip!' The issue is that, if (SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1) returns no reco...

sql update for a field to increment same id occurrence

I am having trouble with accomplishing this: I want to create an update statement to reflect the following: Aid is my unique identifier and LineItem is each occurrence of AID that is incremented. example below: AID | LineItem 1 | 1 1 | 2 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 3 | 1 3 | ...

SQL return if statement

I have thie function and need to know if im going about it the right way. What it does it looks up the last entry into the sql called "LastID" and if the return is the same as "postid" it returns false. function LastPost(div) should look up the last post. and UpDateSQL(div,LastID) checks the if condition else inputs into the database....

Sql SELECT TOP 1

I'm getting weird results from a table I'm selecting from. Consider the following table: USERID TICKETSASSIGNED 1 0 100 0 101 0 102 0 103 0 Now, if I have the following sql as in: SELECT TOP 1 USERID FROM TABLEX ORDER BY TICKETSASSIGNED The result I would expect to get is "1" bu...

Need help creating complex T-SQL SELECT statement.

I am trying to make SELECT statement for following situation and need help to make this SELECT statement. It's SQL Server 2005. When the select statement is run, it should return rows which have SentDate as NULL assuming that there are no duplicate PersonID in table. It will return result set with Status as 'Initial Record' (as the same...

SQL Server DELETE is slower with indexes

I have an SQL Server 2005 database, and I tried putting indexes on the appropriate fields in order to speed up the DELETE of records from a table with millions of rows (big_table has only 3 columns), but now the DELETE execution time is even longer! (1 hour versus 13 min for example) I have a relationship between to tables, and the col...

Postgresql Named windows

The documentation for Postgresql window functions seems to imply you can use the same named window in multiple places in your query. However, I can't figure out how do I create a named window? SELECT first_value(vin) OVER( PARTITION BY vin ) AS w, first_value(make) OVER w FROM inventory.vehicles WHERE lot_Id = 9999 AND make is not null;...

SQL Help - How to "linearize" data?

I'm not sure if "linearization" is the proper term, but I need a query that will output something like this: item_name item_price first_name last_name ----------------------------------------------- 'camera' '100' 'Little' 'Timmy' 'computer' '200' 'Little' 'Timmy' Here's my DB: http://pastebin.com/iS4QKHEb...

Will using the TABLOCK and HOLDLOCK hints in SQL Server 2005 COMPLETELY prevent inserts until the end of the transaction?

I need to retrieve the identity field generated by SQL Server 2005. Usually I would just use SCOPE_IDENTITY or add an OUTPUT CLAUSE to the insert, however neither of these method help in this situation: as there is an INSTEAD OF trigger attached to the table. Next I considered @@IDENTITY, but as there is also another trigger attached to ...

sql records in last few hours

I have records in mysql table and I want to know how many records were in last 3, 7 and 12 hours. What would be the sql for that? My date field in in the format as 2010-08-09 09:52:27 2010-08-09 09:52:27 2010-08-09 09:52:27 2010-08-09 10:44:46 2010-08-09 10:44:46 2010-08-09 11:58:27 2010-08-09 14:48:22 2010-08-09 14:48:22 ...

Linq to Sql DatabaseExists

Hi.... Lets say i have a database in c:\database.mdf what is the difference between using context.DatabaseExists("c:\database.mdf") or just simply checking with File.exists("c:\database.mdf") ? i ask this because i get strange behavior form DatabaseExists method : it tells me sometimes that c:\database.mdf exists but it's not there, ...

SQL Server Read Only Permissions

Hi All, I need to GRANT Read only Permission on few Databases.How Can I acheive this in SQL Server 2005 ? Thanks In Advance Anoop ...

Using C# to Select from SQL database Table

Hello All- I have a List of UserID's and a open connection to SQL Server. How can I loop through this List and Select matching UserID with First_Name and Last_Name columns? I assume the output can be in a datatable? many thanks ...

SQL Syntax error on one machine but not the other

I have a legacy web-site that I am maintaining (built mostly in classic ASP with vbscript). I am making some modifications to it, and got tired of waiting 5 minutes everytime I had to go through one of the pages that was loading 99000 records from the database before displaying the first 20 of them, so I rewrote the code as: sSQL = "WIT...

search a database

Let's say I have a large database with product information. I want to create a search engine for that database, preferably with indexing and autocorrect features. How do I go about doing this? Are there any good libraries I could use, so that I don't have to start from scratch with basic SQL? Just some basic recommendations, links, would...