sql

self join table

HI, i have a table, new_id----old_id----created_on 1234------5678------20100912 5678------3456------20100808 etc. i wrote this query, $q = "select event1.new_id, event1.old_id, event1.created_on,event2.new_id, event2.old_id, event2.created_on FROM replaced_isbns event1 JOIN replaced_isbns event2 ON event2.new_id...

How can I trim certain characters from a string in sql?

I would like to trim all special characters from a string in SQL. I've seen a bunch of people who use substring methods to remove a certain amount of characters, but in this case the length on each side of the string is unknown. Anyone know how to do this? Thanks, Matt ...

Coalesce equivalent in Hibernate Criteria query?

Hi everyone, I want to write the following query as a Hibernate Criteria query: select to_char(nvl(ol.updated_datetime, ol.created_datetime), 'dd/mm/yyyy'), sum(discount_price) from order_line ol where nvl(ol.updated_datetime, ol.created_datetime) between to_date('05-may-10') and to_date('30-may-10') group by to_cha...

How can I get a distinct list of elements in a hierarchical query?

I have a database table, with people identified by a name, a job and a city. I have a second table that contains a hierarchical representation of every job in the company in every city. Suppose I have 3 people in the people table: [name(PK),title,city] Jim, Salesman, Houston Jane, Associate Marketer, Chicago Bill, Cashier, New York ...

sql server query/subquery question

Hi Experts, I have a tables with data like this Id BookId TagId 34 113421 9 35 113421 10 36 113421 11 37 113421 1 38 113422 9 39 113422 1 40 113422 12 I need to write a query (SQL Server) which gives me data according to the tags say if I want bookIds where tagid =9 it should return bookid 113421 and 113422 as it exi...

Get consolidated results with following tables

I have a scenario. Here's my table structure is: ID LoginDate RemovalDate ---------------------------------------- 1 2009/08/01 NULL 2 2009/09/12 2010/01/02 3 2009/08/31 2009/10/29 4 2010/02/17 NULL 5 2009/10/18 ...

Performance: Subquery or Joining

Hello I got a little question about performance of a subquery / joining another table INSERT INTO Original.Person ( PID, Name, Surname, SID ) ( SELECT ma.PID_new , TBL.Name , ma.Surname, TBL.SID FROM Copy.Person TBL , original.MATabelle MA WHERE TBL.PID = p_PID_old AND TBL.PID = MA.PID_old ...

Working by group by for grouping data into string format

I have a table that contains some data given below. It uses a tree like structure i.e. Department SubD1, SubD2 ..... PreSubD1, PreSubD1... PreSubD2, PreSubD2... pk_map_id preferences ImmediateParent Department_Id -------------------- ---------------...

MySQLi PHP: Check if SQL INSERT query was fully successful using MySQLi

Hi, I have this big function that gets a lot of different data and insert it into multiple tables.. Not all the data is always available so not all the SQL INSERT queries are successful. I need to check which SQL INSERT query was fully successful and which wasn't to the do something with this data (like inserting into a log table or simi...

Trouble with CTE

I am learning CTE, and tried out the following query WITH fooCTE AS ( SELECT TOP 5 f.bar FROM foobar f ) But it's displaying an error which is quite incomprehensible. Msg 102, Level 15, State 1, Line 5 Incorrect syntax near ')'. Infact when I run the query below, I get the top five values being displayed. SELECT TOP 5 f.bar FROM...

How do I populate my VS2008 Data Sources window with a LINQ query table?

I´m (professionally) creating a SQL Server database client by using Visual Studio 2008, C# -> Windows Form(s). And I´m using all the built in stuff, provided by my friend VS Studio, dragging and dropping, creating SQL query tables in DataSet.xsd, and so on... I like that. But! I would like to try out LINQ, as I would like to have somet...

script to dynamically fix ophaned users after db restore

After performing a database restore, I want to run a dynamic script to fix ophaned users. My script below loops through all users that are displayed after executing sp_change_users_login 'report' and applys an "alter user [username] with login = [username]" statement to fix SID conflicts. I'm getting an "incorrect syntax error on line 15...

How to avoid Cartesian product in an INNER JOIN query?

I have 6 tables, let's call them a,b,c,d,e,f. Now I want to search all the colums (except the ID columns) of all tables for a certain word, let's say 'Joe'. What I did was, I made INNER JOINS over all the tables and then used LIKE to search the columns. INNER JOIN ... ON INNER JOIN ... ON.......etc. WHERE a.firstname ~* 'Joe' OR a.las...

Violation of PRIMARY KEY constraint 'PK_

hi am trying to write a code in which i need to perform a update but on primary keys how do i achieve it i have written the following code: kindly look at it let me know where m wrong Protected Sub rgKMSLoc_UpdateCommand(ByVal source As Object, ByVal e As Telerik.Web.UI.GridCommandEventArgs) Handles rgKMSLoc.UpdateCommand Try ...

SQL Split function.

Hi guys, I'm looking for a way to do this ... SELECT FirstName, LastName, Split(AddressBlock, ' ', 1), Split(AddressBlock, ' ', 2), PostCode FROM Contacts The arguments I want to pass are ... The address The separator (current situation requires 2 spaces but this might be a comma or a space followed by a comma) or something els...

Sql 2008 Logins

Hello! I was wanderin what is the difference when login with: Server name:User Authentication: windows authentication and Server name:User\sqlexpress Authentication: windows authentication I'm asking because i wasn't able to give sysadmin role to the 'user\sqlexpress' but i could on 'User' Sorry for the question if it is too simple ...

MSBuild: automate collecting of db migration scripts?

Summary of environment. Asp.net web application (source stored in svn) sqlserver database. (Database schema (tables/sprocs) stored in svn) db version is synced with web application assembly version. (stored in table 'CurrentVersion') CI hudson server that checks out web app from repo and runs custom msbuild file to publish/package app...

automatic id generation which is a primary key

in vb.net while entering a new entry i want to assign a record a unique id like in case of numeric i do this way Dim ItemID As Integer KAYAReqConn.Open() SQLCmd = New SqlCommand("SELECT ISNULL(MAX(ItemID),0) AS ItemID from MstItem", ReqConn) Dim dr As SqlDataReader dr = SQLCmd.ExecuteReader If dr.HasRows Then dr.Read() ItemID ...

MySQL subquery and bracketing

Here are my tables respondents: field sample value respondentid : 1 age : 2 gender : male survey_questions: id : 1 question : Q1 answer : sample answer answers: respondentid : 1 question : Q1 answer : 1 --id of survey question I want to display all respondents who a...

Convert historic dates from utc to local time

I have an sql table with data like this: | theDate (datetime) | theValue (int) | ---------------------------------------- | 2010-05-17 02:21:10 | 5 | | 2009-03-12 04:11:35 | 23 | | 2010-02-19 18:16:53 | 52 | | 2008-07-07 22:54:11 | 30 | The dates are stored in UTC format in a datetime ...