database-queries

Complex Sql Query Help. Forming query in SQL

Hello, I am new to SQL and need to write a complex query. Can you please help? I have two tables. One is called PATIENTS and the other one is called CASES. PATIENTS has a "patient number" and a date entered. CASES has "patient number," "case no." and "date modified." The two tables are connected with the "patient number." There are mul...

Display all unique ID's even on 'limit' in MYSQL

Hi, I guess this is strange situation. I have a results table which contain 100k records, basically this table consists of transactions with particular ID's ( not PK). I am displaying the results limiting the records to 3000. I am using Javascript tool box to display a filter on top of table heading so that users can select / filter...

Database Error Handling: What if You have to Call Outside service and the Transaction Fails?

We all know that we can always wrap our database call in transaction ( with or without a proper ORM), in a form like this: $con = Propel::getConnection(EventPeer::DATABASE_NAME); try { $con->begin(); // do your update, save, delete or whatever here. $con->commit(); } catch (PropelException $e) { $con->rollback(); thr...

Get SUM of two columns linked to same column in different table

Hi I have a table "Events" for users to add events with the following fields: EventID EventName EventSlot (--> slotID) EventSlotExtra (--> slotID) (optional) EventLimit And I have another table "Slots" SlotID (int) SlotTime (this is 9am-10am, 10am-11am, etc) I need to query both tables so I can get a total of people attendin...

Sql query to overlay date/period overrides over default dates

Any ideas on building a Sql Server (2008) query that will give me say the "date specific prices for an item based on the default or override where exists". So a Default table might look like this - columns Price, StartDate, EndDate (yyyy-M-d): Default: $10, 2010-1-1, 2010-2-1 The Override table like this: Override: $12, 2010-1-5, ...

Breakdown of time spans between a list of Datetime's

Hello, I have a SQL Server 2005 database table, which has a datetime column. I write an entry in this table every time a service processes an incoming request. What I would like to get is a breakdown of the time spans between sequential entries. If the data is: 2009-10-30 04:06:57.117 2009-10-30 03:52:44.383 2009-10-30 03:42:00.990 2...

Pivot rows to columns based on content in Oracle 10g PL/SQL

I have a table in my database, user_answers that stores users answers to a series of questions, with rows; user_id, question_id, answer_id and text_entry. Question text and answer text (if any) are stored in lookup tables. There are three types of questions, single-answer questions, multiple-answer questions and text-entry answer quest...

Web App Saving Entire Table At Once

Set the scene: New to .NET; drinking from firehose ASP.NET MVC app, SQL Server back Editable table in browser with a single SAVE button. User can right-click to add or delete rows. Table won't ever have more than approx. 30 rows. My question : I'm saving everything upon the Save button click but would it be better to save row by ro...

Advanced indexing involving OR-ed conditions (pgsql)

I'm starting to get a much better grasp on PostgreSQL indexing, but I've run into an issue with the OR conditional, where I don't know how to go about optimizing my indexes for a faster query. I have 6 conditionals that, when run individually, appear to have a small cost. Here's an example of the trimmed queries, including query plan c...

MySQL count problem

I have a table course: CREATE TABLE course (COURSE_NO NUMERIC(8,0) NOT NULL ,DESCRIPTION VARCHAR(50) ,COST NUMERIC(9,2) ,PREREQUISITE NUMERIC(8,0) ,CREATED_BY VARCHAR(30) ,CREATED_DATE DATE ,MODIFIED_BY VARCHAR(30) ,MODIFIED_DATE DATE ,PRIMARY KEY (COURSE_NO) ,INDEX (PREREQUISITE) ) TYPE...

categorize image and video files as albums

I want to categorize image and video files as albums .My application is developed in asp.net and sql2005.Now i am uploading the image s and videos .My aim is to the user can create an album and he should upload files to that album .There have any way to do this?I am using to show the files that uploaded in repeater as links that are popu...

Codeigniter WHERE on "AS" field

I have a query where I need to modify the selected data and I want to limit my results of that data. For instance: SELECT table_id, radians( 25 ) AS rad FROM test_table WHERE rad < 5 ORDER BY rad ASC; Where this gets hung up is the 'rad < 5', because according to codeigniter there is no 'rad' column. I've tried writing this as a cust...

SQL query to get all the data in specific range.

I have a table(offer) with three columns, id, product_id and price. offer ----- id (integer) product_id (integer) price (decimal) I wanted to fire a SQL query which will return the number of offers between a price range. range should be like 0-1, 1-2, 2-3 etc price_lower price_upper number_of_offers ------------------------...

How do you select all columns, plus the result of a CASE statement in oracle 11g?

I want to select *, and not have to type out all individual columns, but I also want to include a custom column with a case statement. I tried the following: select *, (case when PRI_VAL = 1 then 'High' when PRI_VAL = 2 then 'Med' when PRI_VAL = 3 then 'Low' end) as PRIORITY from MYTABLE; But...

Abstracting a Foreign Database reference

I want to query database two from database 1. Easy, right? SELECT * FROM database2.dbo.TableName Sure. But what if the actual name of database2 changes? I have to go back and change all of my procs. Imagine if the instance of database2 on the staging server is named "database2_staging"... What I'd like is an abstraction that I could p...

How to Represent Rules using a MySQL Table?

I have a table of 100,000s of USERS (name, age, gender, phone, company, street, city, state, country, zipcode, etc). I also have a table of thousands of PROMOTIONS which are offered to users. Now, for each promotion I need to add a rule which defines which subset of users it applies to. For example, a rule might be: All users which hav...

What database APIs in C++ and C# are equivalent to JDBC in Java?

I have not done database programming in C++ and C# before, but did some in Java. Now I am asked to figure out the options to do it in C# or C++ (not sure which one yet). We may use the MySQL RDBMS. I searched online and found .NET SQL Data Provider, OleDB and ODBC. What other APIs exist? What's your recommendation? Do I need to buy s...

Is it possible to order_by with a callable?

DUPLICATE: http://stackoverflow.com/questions/981375/using-a-django-custom-model-method-property-in-orderby I have two models; one that stores posts and another that stores votes made on those posts, related using a ForeignKey field. Each vote is stored as a separate record since I need to track the user and datetime that the vote was...

Most efficient way to insert two rows that depend on each other.

I have a site that is essentially a collection of links. A mysql database which stores users, links and votes. My links table has two foreign keys, user_id and vote_id. When a link is inserted into the database it will start with one vote so that means I need to insert a row into the votes table but they essentially depend on one anot...

Select From One Table And Include Sum Value From Another

My title is terrible but I'm having a hard time wrapping my head around this. I have two tables. Links and Votes. Links contain info about links submitted and the usual jazz, votes contains a foreign key link_id and a karma_up and karma_down value (both unsigned ints). Because I want to limit votes to one per user and record vote tim...