sql

MySQL, Get users rank

Hi Guys, I have a mysql table like below: id name points 1 john 4635 3 tom 7364 4 bob 234 6 harry 9857 I basically want to get an individual user rank without selecting all of the users. I only want to select a single user by id and get the users rank which is determined by the number of ...

Problem with SQL query

Hi, I have the following: <?php $result = mysql_query("SELECT `category_id` FROM `categories` WHERE `category_parent_id` = '80'"); while ($row = mysql_fetch_array($result)){ $childrows [] = $row['category_id']; $clean = array_unique($childrows); $category_string = implode(",",$clean); echo $category_string; ?> And this outputs: 4...

Why would a database timeout?

when i run queries to the database im constantly getting this System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Generated: Tue, 18 Aug 2009 08:05:39 GMT System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledExcept...

Type Conversion in Persisted Computed Column

Hi everybody I'm working with 2 related tables in a Microsoft SQL Server 2008 environment which are connected via a GUID. In one table, the field has the type varchar(50), the other one is properly types as uniqueidentifier. This is obviously bad but I can't change this now because it's given by a legacy piece of software. The conversi...

Should I use SQL cursors here?

I have recently read about how cursors should be avoided. Well, I would like to know if my usage of them is appropriate. I am creating a scripting engine that will work online(embedded in a page, server-side) This scripting engine will be used by "advanced" end users of this product. The product works very heavily with the database howe...

Using Spring's JDBC support to get output parameters

I'm using Spring's JDBC support to run SQL queries and updates on an Oracle database. I'd like to insert a row and then get the key that it was assigned (using an Oracle sequence). In normal JDBC code, I would include a RETURNING INTO clause and then register an output parameter (well described here) However, I would like to just use ...

if statements in sql query

Good morning all. I have an issue with a query. I want to select something in a query only if another field is somethingelse. The below query will better explain select Case isnull(rl.reason,'Not Found') When 'D' then 'Discontinued' When 'N' then 'Not Found' When 'I' then 'Inactive' When 'C' then 'No Cost' When '' then 'Not ...

Handling Multi Users (issuing invoices)

Handling Multi Users Requirements: I have an applications (mysql php jquery) where the users can: Review records and update certain fields. Issue invoices by selecting orders. Issues: The issue is that an invoice should not be issued twice for the same time period. Also, a field should not be updated by two or more users at the sa...

does sqlite supports any kind of IF(condition) statement in the select

does anyone know if sqlite supports the sql function "if" in the select.. for example select if( length( a ) > 4 , a , ' ') as b from foo which would return a if the length was over 4 chars long.. or else it would return ' ' as b if it doe support a condition in the select what is the syntax is should be using? i have checked ht...

Remove rows NOT referenced by a foreign key

This is somewhat related to this question: I have a table with a primary key, and I have several tables that reference that primary key (using foreign keys). I need to remove rows from that table, where the primary key isn't being referenced in any of those other tables (as well as a few other constraints). For example: Group groupid...

Max date in view on left outer join

Thanks to a previous question, I found out how to pull the most recent data based on a linked table. BUT, now I have a related question. The solution that I found used row_number() and PARTITION to pull the most recent set of data. But what if there's a possibility for zero or more rows in a linked table in the view? For example, t...

Calculating a row position in the table

Let's say I have a table like this (this is just a simplified example, the real table I'm talking about is much more complex): CREATE TABLE media ( id INT NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, description TEXT NOT NULL, voted INT NOT NULL DEFAULT 0, rating FLOAT NOT NULL DEFAULT 0 ) ENGINE = INNODB; The voted column re...

Cleaning up a database

I have a database with hundreds of tables. I am building a script to delete all of the rows in this database. Of course, being a relational database, I have to delete rows from the children before I can touch the parents. Is there something I can use for this or do I have to do this the hard way? EDIT Accepted Answer was modified t...

SQL Prepared Statements for complex filtering

Ok, so I know you should use prepared statements to avoid injection attacks as well as for the huge speed improvements, but there is still one place that I find myself doing string manipulation to build my sql query. Suppose I have a search feature that consists of six or seven or n dropdown lists. Based on whether there is a default v...

Directly fetching a list from a column's data with MySQL

Hello, Let us suppose I have to deal with lots of grandmothers that have lots of cats. I have a table granny_cat : granny_id | kitty_name -------------------- 1 Kizzy 1 Jimmy 1 Katty 2 Georges 2 Albert 3 Panther and I want to retrieve a list of granny 1 cat's, i.e. to get so...

Good resources for learning PL/pgSQL?

I've been looking around the net trying to find good resources for learning PostgreSQL's procedural programming language, PL/pgSQL. So far the only thing I've managed to dig up is the tutorial in the PostgreSQL documentation. While that is good, I've been looking for something more in-depth. Can you recommend anything? ...

How can I find every row in a table where the value of a field is contained in a specific string?

Usually, I use SELECT * FROM tableName WHERE fieldName LIKE '%string%' to find every row in tableName where the value of fieldName contains the string 'string'. How can I find every row in tableName where the value of fieldName is contained in the string 'string'? For example, in the following table names FirstName | LastName Bob ...

group by clause help

I am coding facebook kind request page for a project. I am stuck with grouping requests in all requests printed page. my sql as SQL = "SELECT R.REQUESTID, R.BYID, R.TOID, R.TYPE, R.DATEENTERED, M.MEMBERID, M.FIRSTNAME, M.LASTNAME" SQL = SQL & " FROM REQUESTS R, MEMBERS M" SQL = SQL & " WHERE R.ACTIVE = 1 AND R.TOID = "& Session("MEMB...

Using NOT keyword with full-text search and multiple columns

Is it possible to use the CONTAINSTABLE and NOT keywords with SQL Server full-text searching and exclude rows where a single column contains the value to be excluded? For example, let's take the following indexed view (simplified for the purposes of illustrating this problem): ItemId INT FirstName VARCHAR(200) MiddleName VARCHAR(200) L...

union with join to common table

I've got a query that presently looks something like this (I'll simplify it to get to the point): select shipment.f1, type1detail.f2 from shipment join type1detail using (shipmentid) where shipment.otherinfo='foo' union select shipment.f1, type2detail.f2 from shipment join type2detail using (shipmentid) where shipment.otherinfo='foo' ...