sql

How to retrieve a cluster of connected users from table of connections between two users?

Hi, The table consists of pairs of users which are connected together. The following is a hypothetical example: user1, user2 a, b a, c c, a c, d d, e a, e j, n g, n f, n By randomly picking up a user from the table (user1 or user2) I would like to retrieve the whole cluster of connections to which the selected user belongs. For examp...

ERD diagram and SQL relationships linking user, project and dataset tables.

Hi guys, I have several tables in my ERD which which I would like to combine in a relational manner. I have several use cases, but I completely lost track of what kind of relations to use between the tables. Every user can work on multiple projects. Every user has one specific role per project (Manager, Contributor, User) Every proje...

Setting the seed value for an identity column in visual studio/entity framework w/SQL Server 2008R2

Is it possible to override the seed value when creating a new table using the designer in visual studio? I know it does not really matter from a programmatic point of view, but I don't like when ID's start at 1. I don't want to see customer ID of '1' or a sales order of 3 - I prefer them to all be the same length - i.e. 4 or 5 or 6 di...

SQL word root matching

Hi all, I'm wondering whether major SQL engines out there (MS SQL, Oracle, MySQL) have the ability to understand that 2 words are related because they share the same root. We know it's easy to match "networking" when searching for "network" because the latter is a substring of the former. But do SQL engines have functions that can mat...

Check for integrity constraint violation in SQL before deletion

My question is sort of similar to this one I want to check if a DELETE query will fail because of a constraint violation. I'd like to do this on database level because I think letting it fail and catching the error is ugly. Another option is "manually" checking for it using SELECT queries to see if there are constraints, but this is ra...

WHERE IN Question

I have a couple of Tables already supplied in the form MainRecord Record ID Details Textual Information 1 AAAAAAA ... some text referring to Oxford St Giles... 2 BBBBBBB ... some text referring Oxford.... 3 CCCCCCC ... some text referring to Oxford St Aldate... and supporting table Pl...

ORACLE SQL: Replace part of text field ignoring case

I need to replace a path stored in an Oracle DB text field. However paths have been specified with different cases (ie MYPATH, MyPath, Mypath, mypath). When using a combination of REPLACE and UPPER, it does not work as I need it to, ie: UPDATE Actions SET Destination = REPLACE(UPPER(Destination), 'MYPATH', 'My_New_Path') This does th...

Best way to detect which columns was updated when using triggers

Hi There I have have a user table, that has |Firstname|Surname|DateOfBirth| ... and a few other columns. On that specific table i have a trigger on update that if someone changes any user values it creates an audit. My problem is, management want's a visual audit displayd int the following way: DateChanged |Change ...

select the sum of every client's last order

Hello, My data is as follows: ORDER_ID CLIENT_ID DATE VALUE 1881 51 2010-07-19 100.17 1882 50 2010-07-19 100.17 2754 50 2010-07-25 135.27 2756 50 2010-07-25 100.28 5514 50 2010-07-27 121.76 5515 50 2010-07-28 109.59 5516 50 2010-07-27 135...

Using same tables alternatively in IN clause returns different results - I was expecting same!!

I have read two excel sheets in two temp tables, #temp and #temp1 Replacing the tables in IN Clause returns different resutls. I was expecting the same. Can anyone explain? select * from #temp where name in (select comp_name from #temp1) Returns 473 records. select * from #temp1 where comp_name in (select name from #temp) Returns 1...

Is table alias ambiguity in SQL prohibited in standard or blocked by implementations?

Yesterday I ran into an ambiguity issue in SQL for the first time. I got used to SQL errors in case of table ambiguity and yesterday did not even try to think about the problem in that way. Here is a simplified query: SELECT val1, ( SELECT SUM(val2) as val2_sum FROM ( SELECT 1 as id, 10 as val2 UNION ALL S...

Simple query optimization

Hey guys, forgive me if this is too simple a question. I basically want a count of the number of males and females in a database. So I know two simple queries will accomplish this, such as: select count(*) from table where gender='male' select count(*) from table where gender='female' However, this seems very inefficient since I know ...

Query to find a weekly average

I have an SQLite database with the following fields for example: date (yyyymmdd fomrat) total (0.00 format) There is typically 2 months of records in the database. Does anyone know a SQL query to find a weekly average? I could easily just execute: SELECT COUNT(1) as total_records, SUM(total) as total FROM stats_adsense Then just d...

sql-server-2008: how do i sort the results of a procedure?

i am running: exec sp_who go is it possible for me to sort by a field like: exec sp_who order by dbname go ? i would i accomplish this? ...

WPF Two-way binding to a dataset - updating the database?

Okay, so I'm fairly new to WPF and data binding, but I've searched and searched and can't seem to find an answer to this. I have a database (called Inventory), and a dataset (called DevicesDataSet). What I'm trying to do is bind the dataset to a listbox, so that a specific device from the Devices table of the DevicesDataSet can be sele...

Stupid sql error.

Hello. I have this sql statement: SELECT game.game_id FROM game, userGame WHERE userGame.user_id = 1 AND userGame.game_id != game.game_id; And I'm getting this: 1 1 2 2 3 3 4 4 4 5 5 5 I'm running the statement on a Sqlite3 database on Android 2.1. The tables are: USERGAME -------- userId gameId more_columns GAME ----- gameId m...

How does ER/Studio compare to VS2010 tooling?

I'm new to the world of data and DBA work (I'm mostly an .NET developer)... we have an old version of ER Studio (7.5) and I'm wondering how it compares to what comes with VS 2010. Your experience is much appreciated! Also, feel free to reccommend tags for this post ...

SQL Server 2005: stored procedure to move rows from one table to another

Hello, I have 2 tables of identical schemea. I need to move rows older than 90 days (based on a dataTime column present in the table) from table A to table B. Here is the pseudo code for what I want to do SET @Criteria = getdate()-90 Select * from table A Where column X<@Criteria Into table B --now clean up the records we just moved ...

How is the Pop up alert which is displayed on stackoverflow when you are answering a question and someone submits another answer created?

Ok, so you know when you're answering a question and are in the middle of typing it, and someone else posts an answer to your question and you get a little popup that says there is a new answer to the question? My question is how do you do that? I think I have the basic concept down... A question is answered, added to the database. The p...

Stored procedures and Triggers in PostgreSQL

I never used these two features in PostgreSQL. Which language is used to write stored procedures and triggers in PGSQL? Is it the same that Oracle or SQL Server follows or it is C? ...