sql

Need a query to move all the emails from one table into another using a common row value as the link

An import was performed on my database which wasn't done so well. It's resulted in some fields being empty where they shoudln't, etc... What I need to do now is move all the data from one column in one table into a column in a different table. Both tables have an ID which is a link to each rows. For example: Table1 id | linkID | emai...

Using .Include() when joining a view using Entity Framework

Im using an Indexed View which is an entity without any relations (relations to my table entities would be preferable but this seemed to be nearly impossible to achieve). This view consists of 4 FK's which together form the PK: PortalID CategoryID BranchID CompanyID Now im joining this view to select a set of companies like this: var...

Help with database structure

I am trying to create a system which allows local network users to collaborate content together. Requirements: The user should be able to: create content, edit content (content created by anyone), records should be kept on who edited what and when, should easily be able to go back and forth between edits, add comments to pages, sho...

Insert row into table if it is empty and replace the existing row if it isn't in SQLite

I have a table in SQLite 3: CREATE TABLE foo ( bar TEXT ); It always has 0 or 1 rows. I want to write a query which updates the row if there is 1 row and adds the row if there are 0. What is the cleanest way to do it? ...

Order of tables in join query

I found this paragraph in Oracle documentation if you want to select the name of each department along with the name of its manager, you can write the query in one of two ways. In the first example which follows, the hint /++ordered++/ says to do the join in the order the tables appear in the FROM clause with attempting...

select overlapping datetime events with SQL

Hi. I have a SQL table Events (ID int, Event int, StartTime datetime, Duration int). Event is event code (1=system running, 2=break) Duration is the amount of seconds that the event was active. I'd like to get the amount of seconds that event 1 was active, but subtract the duration of event 2. E.g. event 1 was from 1:00 to 6:00, even...

?? operator in sql

Hi! I have the following issue : We have this query : select price*hours as payment from table employees Now, if the result of that multiplication it's 0, I'd like payment to be "x", not 0. Translated in nonsql this would mean : (price*hours) != 0 ? (price*hours) : "x" Any ideas how could i implement this sql command ? Thanks! ...

SQL Query for finding a unique Item in one of many tables

I have the need to search for an item that exists in one of many (~20) tables I can safely assume that the item only exists in one of the tables, all tables contains an index with the same column name and value type. Tables vary in on of their columns, containing one of a few possible value types (int, string, binary. etc.) What would ...

How do I reference a row from one in my second query

I have two queries Dim query1 as String = "Select * from openquery (devbook, 'SELECT start_date FROM bookings WHERE investor = ''00000'' ')" Dim FlightQuery as String = "Select * from openquery (devbook, 'SELECT price FROM flight_prices WHERE dep_date = start_date ')" Where it says dep_date = start_date I need to grab the start_date ...

Actions log table structure

Hi, I have to write a service which will implement some kind of a black/white list manager. Short description : It is very similar to messaging in social networks. imagine by default anyone can send you a message. And you have the possibility to block it for everyone, so no one can send you a message. Once it is blocked you can have a ...

Searching with Join in Rails 3. Please help!

Hello! I am developing a small application in Rails 3. In this application users can be connected to each other. I am mainly using two tables to store user data and relations. I want to search these tables with either firstname or lastname to get the contacts of a user. Table ONE - Profiles In this table I am storing Firstname, Lastna...

Is there a round-trip software for PHP programmers out there?

I search for a round-trip programming solution which does generate PHP and SQL code from a UML Model It sould also include state and activity diagrams which were compiled into PHP/SQL. Or does someone know some software who offers the possibility to create my own PHP / UML interpreter? thanx in advance ...

update table with random values

I have two tables, lets say Table1(Col1,Col2,Col3,Col4) and Table2(Col1). I want to update some cols in Table1. For each row in Table1 where Col1 ends with '001' (assume all values are at least length 4) I want to: 1.place a random number from the set (10,20,30,40,50,60,70,80,90) in Col2. 2.place a random 9 digit number in Col3. ...

SQL request to find the n last records of each one of a user's friends

Hi, I have a User model which has friends (User.friends with a user_id/friend_id join table called followings). Each user has events (the events table has a user_id column). For an activity feed, I want to get an array with the last 20 events of the friends of the user. Right now I'm calling the last 20 events for each friend, sorting ...

SQL SELECT Query for a Total and parts building the total

Hello! I need a SQL Server Query for the following scenario: The Tables: CREATE TABLE [dbo].[JobStatus] ( [OID] [int] IDENTITY(1, 1) NOT NULL, [Name] [varchar](100) NOT NULL, [Code] [varchar](5) NOT NULL, [PictureID] [int] NOT NULL, [LastModifiedAt] [timestamp] NOT NULL, CONSTRAINT [PK_JobStatuses]...

Synchronize New Database Schemas with existing DB using SQL Script

I have a database with Data and I have another same database, but I did changes in tables, relations etc. Now I want to update DB schema with the new one that I have modified, but using SQL Script. ...

How to kill or rollback active transaction?

Hi all, How can i kill or rollback, uncommitted transaction? I am listing my active transactions with the following sql: SELECT * FROM sys.dm_tran_session_transactions My result is: session_id transaction_id transaction_descriptor enlist_count is_user_transaction is_local is_enlisted is_bound -------------------------------...

Converting data types in SSIS

I get an input CSV file that I have to upload to my oracle database. Here is some sample data ContractId, Date, HourEnding, ReconciledAmount 13860,"01-mar-2010",1,-.003 13860,"01-mar-2010",2,.923 13860,"01-mar-2010",3,2.542 I have to convert the incoming column to DB_TIMESTAMP (to match the structure in the destination table). But w...

Convert number to time

How to convert number 1.33408564814814 to time 32:01:05? ...

Constraint is key is index is constraint?

in SQL Server Management Studio (SSMS) 2008 R2 Dev on the table (because I cannot ask without it) --SET ANSI_NULL_DFLT_ON ON create table B (Id int) I create unique constraint ALTER TABLE B ADD CONSTRAINT IX_B UNIQUE (ID) WITH (IGNORE_DUP_KEY = ON) SSMS shows that I do not have any constraint but have a key + index instead w...