I have a simple contest sign-up web form. Each contestant can check a box to be eligible to win one of 20 prizes. Upon submitting a form, a row is created in the "contestants" table. For each prize they have checked, a row in the "entries" table is created. I am trying to make a "results" page that lists all prize names, and below each ...
I have a table like so: call_activity ( call_id TEXT, activity_type TEXT, activity_time TIMESTAMP, PRIMARY KEY(call_id, activity_type, activity_time) ) activity_type may be one of about 9 different strings: 'started' 'completed' (about 5 variations on this) 'other' (these are the states that I want to display) A...
Our company uses Servoy for rapid application development. It is a 4GL environment, that can target web-applications running in Tomcat as well as Java applications, all from the same program. While it is standards based, some aspects of the generated code are proprietary, which couples those applications to the Servoy environment and to...
Hi all, I'm looking for best practices and ideas for managing data in a database table during a bulk data upload. I think it would be easiest to start with an example: Suppose the database table in question contains the following records: id | name | ... 1 | company A | 2 | company B | 3 | company C | I then opt to bulk upd...
In the answer to a previous post (Tuning Rows-to-Cols Query), I learned how to more efficiently construct a row-to-cols query which allows for filtering by date. However, I now need to take this one level further. The schema for the query below is as follows: SAMPLE (1-to-many) TEST (1-to-many) RESULT (1-to-MANY) Each sample has one or...
I've got a database full of datetime2 columns than needs to be moved to a SQL 2005 database. So, I need to convert all these datetime2(7) columns to datetime. How can I go about doing this? Right now I've managed to select the table name and column name for all columns with the datetime2 datatype like this: SELECT t.name, c.name, i.DA...
I'd like a query that at its simplest will join 2 tables together with no other express relationship other than each row in what I will call the "pool" table will match to precisely 1 row in my other table. I don't care which row, I just want every row in my primary table to get a single row from the "pool" table and know that each row f...
Let's say I have a table with a bunch of dates, e.g.: declare @tbl table { idx int primary key, startdate datetime, enddate datetime } And I want to find the largest set of rows where startdate and enddate intersect (in the real world, the start date and end date represents start and end times for events, and I need to fin...
Say I have records with IDs 3,4,7,9 and I want to be able to go from one to another by navitagion via next/previous links. The problem is, that I don't know how to fetch record with nearest higher ID. So when I have record with ID 4, I need to be able to fetch next existing record, which would be 7. The query would probably look somethi...
We are a small company of 150 employees and I am searching for a hosted, inexpensive Issue Tracking system for all employees, both in the office and who work remote. Does anyone know a reliable product/host to use? We work off a SQL database and would like to be able to open, submit, track, resolve, share, complete and then close issues...
we have some performance issue with a program, I traced tsql using sql server 2005 profiler and find below result for an INSERT statement, cpu: 0 read: 28 write: 0 duration: 32804 I guess it's because the inserted table has several indexes and it's getting big. is there any other possibilities that I should check? thanks. ...
I'm about to lose it with this stupid mysql statement. Can anyone please tell me what it wrong with this statement please? SELECT * FROM contacts INNER JOIN ind_categories ON contacts.vendor_id = ind_categories.vendor_id AND ind_categories.category_id = "retail" AND WHERE zipcode in ( " 93044,93041,93043 " ) ...
i cannot see why this would be an issue. i use the msgbox to display my strSQL, but it looks perfectly fine. Dim strSQL As String strSQL = "INSERT INTO Jobs (Date, RefNo, ProjectID, PManager, PDesc, PONo, Src_Qty, WC_Qty, Rate, Total, Note, Company) " strSQL = strSQL & "VALUES (" & JobDate.Value & ", '" _ & T...
I am doing left outer join to my table with around 5 different tables and I need result from only one of these 5 tables. Now I know I could use Coalesce to get what I want. However, I was wondering if there is a way to tell SQL to not keep on joining as soon as one left join hits. However, the left outer joins need to occur in the same...
SELECT V.V_CONTACT, V.V_PHONE FROM VENDOR AS V, PRODUCT AS P WHERE P.P_CODE = 'P1' AND V.V_CODE = P.V_CODE; ...
How do I SELECT rows and return all the columns, instead of specifying individual column names? Can I also return only columns that match a specific criteria? (eg. col names beginning with an "_") ...
I'm joining 3 tables. the last one contains several photos per item. The idea is to get one record per item with only one photo selected at random. The ORDER varies, the WHERE varies. The best I could get is a subquery. There is a more effective way of doing this? SELECT * FROM ( SELECT * FROM art LEFT JOIN prov USING ( prov...
I have a SQL*Loader control file that has a line something like this: FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '#' Normally, I'd use a quotation mark, but that seems to destroy emacs's python syntax highlighting if used inside a multi-line string. The problem is that we are loading an ADDRESS_LINE_2 column where only 7,000 out...
(Using MySQL 4.1.22) I can't get this query of mine to use an index on a large table(200k+ rows), it is doing a full table scan on it. The query takes about 1.2 seconds right now. I want to get it to less than .2 seconds if possible. Here is my query: SELECT st_issues.issue_id, st_issues.cat_id,st_categories.name AS cat_name, st_issue...
I need to delete a bunch of data, and don't have the disk space for the log to continue growing. Upon looking into the matter further, it looks like there isn't any way around this, but I thought I'd ask for sure; it's difficult for me to believe that something so simple is impossible. I tried looping, deleting in chunks, and calling s...