database-queries

SQL Server 2005 implementation of MySQL REPLACE INTO?

MySQL has this incredibly useful yet properitary REPLACE INTO SQL Command. I wonder: Can this easily be emulated in SQL Server 2005? Starting a new Transaction, doing a Select() and then either UPDATE or INSERT and Commit is always a little bit a pain in the a.., especially when doing it in the application and therefore always keeping 2 ...

PostGreSQL - Rename database

Hi, I need to rename the database but when I do in PGAdmin : ALTER DATABASE "databaseName" RENAME TO "databaseNameOld" it told me that it cannot. How can I do it? (Version 8.3 on WindowsXP) Update The first error message : Cannot because I was connect to it. So I selected an other database and did the queries. I get a second error ...

ORA-01031: insufficient privileges when creating package

I'm getting ORA-01031: insufficient privileges when creating a package my own schema. Shouldn't I have complete control over my schema. If this is not the case, what privileges does my schema need? Thanks in advance. ...

How do I declare a multi-column PK in MySQL

I'm trying to create a table with two columns comprising the primary key in MySQL, but I can't figure out the syntax. I understand single-column PKs, but the syntax isn't the same to create a primary key with two columns. ...

Query showing list of associations in many-to-many relationship

Please note that the same question already has been asked in http://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value, but the solution involves creating a function. I am asking if there is a way to solve this without having to create a function or a stored procedure. I have ...

How do you sort a tree stored using the nested set model?

When I refer to nested set model I mean what is described here. I need to build a new system for storing "categories" (I can't think of better word for it) in a user defined hierarchy. Since the nested set model is optimized for reads instead of writes, I decided to use that. Unfortunately during my research and testing of nested sets...

What are some best practices for querying binary data from a database?

I'm being asked to add queryability to a database (Oracle) filled with mostly binary data. So I need to be able to query binary ranges within a blobs of a few kilobytes. I've never done this before, so I'm wondering what are some good practices and pitfalls to consider when starting a project like this. Thanks ...

How to query an Oracle table from SQL Server 2000?

A colleague would like to query a table in an Oracle database using SQL Server 2000, Enterprise Manager. He knows how to do the whole DTS thing, but doesn't want to go down that route for add hoc queries. Is there another way? ...

How do I compress this Oracle resultset into values according to row priority, ignoring nulls?

I'll simplify the problem as much as possible: I have an oracle table: row_priority, col1, col2, col3 0, .1, 100, {null} 12, {null}, {null}, 3 24, .2, {null}, {null} Desired result: col1, col2, col3 .2, 100, 3 So according to the priority of the row, it overrides previous row values, if given. I'm attempting to work out a solutio...

SQL - How to make a conditional INSERT

Using only MySQL, I'm seeing if it's possible run an insert statement ONLY if the table is new. I successfully created a user variable to see if the table exists. The problem is that you can't use "WHERE" along with an insert statement. Any ideas on how to get this working? // See if the "country" table exists -- saving the result to a ...

Combining queries in MySQL

Hey all, I know you can combine multiple table-selects using a Join statement but is there a way to combine these two queries into one? SELECT Statistics.StatisticID FROM Statistics ORDER BY `SubmittedTime` DESC LIMIT 0, 10 And this one? SELECT COUNT(Votes.StatisticID) FROM Votes WHERE Votes.StatisticID = ? (fluff removed) At th...

Using django how can I combine two queries from separate models into one query?

In my specific case, I have two kinds of "messages" that I need to retrive and paginate. Let's omit the details, and just say that the first kind is in a model called Msg1 and the other is called Msg2 The fields of these two models are completely different, the only fields that are common to the two models are "date" and "title" (and o...

Novice SQL query question for a movie ratings database

I have a database with one table, like so: UserID (int), MovieID (int), Rating (real) The userIDs and movieIDs are large numbers, but my database only has a sample of the many possible values (4000 unique users, and 3000 unique movies) I am going to do a matrix SVD (singular value decomposition) on it, so I want to return this databa...

MS Access - Mass Emailing?

I'm using MS Access to create a database with over 5000 contacts. These contacts are separated up into which employee the contact belongs to, and then again into categories for easy searching. What I want to do is create a button that will open up a query in table form (simple), then have check boxes so an employee can select, for exampl...

Is 20 SQL Queries per page load really considered a lot?

I was reading Jeff Atwood's blog on Behold WordPress, Destroyer of CPUs and saw that many people there considered 20 SQL Queries per page load to be a lot. What is the average amount of queries per page nowadays for a highly dynamic page with auto suggest, auto refreshing of data, customized pages, and the kitchen sink? For a simple ...

Inserting rows in table that has a relationship with another table

In my database schema I have an entity that is identified. The identifier can be reused and thus there is a one-to-many relation with the entity. Example: A person can have a nickname. Nicknames are not unique and can be shared amongst many people. So the schema might look like: PERSON id name nickname_id NICKNAME id name The issue i...

PHP - Query single value per iteration or fetch all at start and retrieve from array?

I have a function that looks something like this: //iteration over scales foreach ($surveyScales as $scale) { $surveyItems = $scale->findDependentRowset('SurveyItems'); //nested iteration over items in scale foreach ($surveyItems as $item) { //retrieve a single value from a result table and do some stuff ...

SQL Server - Query Short-Circuiting?

Do T-SQL queries in SQL Server support short-circuiting? For instance, I have a situation where I have two database and I'm comparing data between the two tables to match and copy some info across. In one table, the "ID" field will always have leading zeros (such as "000000001234"), and in the other table, the ID field may or may not h...

How do I create a "filter by price" type of query?

I'm using VBScript (ASP Classic) and SQL Server; I'm trying to have a section on the website where you can see a count of products at certain price levels. Something like: $50 - $100 (4) $100 - $500 (198) $500 - $1000 (43) For the love of me I cannot figure out a good way to do a query like this. I mean... I know how to get the numb...

Retrieving a list of blog posts with related tags with less query

Hi all, image this two tables: Table: Item Columns: ItemID, Title Table: Tag Columns: TagID, ItemID, Title Which is the best way (without changing table structure (yes, I don't mind if they are not normalized)) to retrieve a list of items with all their tags attached using less possible query (i.e. not doing 11 queries to retrieve 10...