Table Self-joins With Updates
Sep 2, 2005
table = PEOPLE
Name Money Type
----- ----- ----
Steve 400 R
Steve 100 R
Paul 500 R
Paul 100 R
Matt 500 R
Matt 200 R
Matt 0 T
Steve 0 T
Paul 0 T
I'm trying to add-up all of the Money values for each Name and store them into their names, but under Type 'T'.
after the update command it should look like this
Name Money Type
----- ----- ----
Steve 400 R
Steve 100 R
Paul 500 R
Paul 100 R
Matt 500 R
Matt 200 R
Matt 700 T
Steve 500 T
Paul 600 T
View 1 Replies
ADVERTISEMENT
Oct 30, 2007
Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen
View 5 Replies
View Related
Jun 25, 2007
Hi, all experts here,
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
With best regards,
Yours sincerely,
View 11 Replies
View Related
Jan 22, 2008
I have a backup application that uses SQL as the backend (both 2000 and 2005) The backup application performs a specific function that enters data into the Database but does not have any reporting. I am looking for a way to query the DB directly to see if there is any info I can grab. But the problem is I don't know where its stored. So my questions) are:
Is there anyway to tell what tables get updated by a certain process. For example if I run this one action how could I then tell what tables were effected or even what data was changed. I tried looking for a logging function that would list this but did not find it. I also tried looking for some type of real time monitor. I even tried looking for a way to search for records / tables that had been recently updated.
I am new to SQL so not sure I am using the correct terms but any help would be appreciated. Also this is SQL2000 and a test server
Thanks in advance for your time
View 1 Replies
View Related
Jul 20, 2005
I'm working on a project that is being built piece by piece. The first partis in place. I will occasionally need to either change thing within a table(only adding fields) or add stored procedures etc.What is the best way of making these changes to the production databasewithout interfering with the existing data? The users are remote and I won'tnecessarily have direct access to the server. I'll have to walk the primaryuser through the process.TIA--Jake
View 1 Replies
View Related
Oct 31, 2007
I need to update information for a user and if the user is classified
as a primary (@blnPrimary) then I need to update information for all
users within his agency (AgencyUniqueId). The issue is that the second
UPDATE to "cdds_User_Profile" always returns a rowcount of 0 (should be
1) even though the values for "@Original_AgencyUniqueId" and
"@Original_UserId" are correct. This is just a snippet of the whole
procedure. I'm trying to implement similar logic in other parts of the
procedure and I'm observing the same behavior there as well. Any help
anyone can provide is greatly appreciated. </p><pre>/*** Update User Profile ***/UPDATE [cdds_User_Profile] SET [FirstName] = @FirstName, [LastName] = @LastName, [Title] = @Title, [Phone] = @Phone, [AcctType] = @AcctType, [AcctStatus] = @AcctStatus, [LastUpdatedDate] = GETDATE() WHERE ([FirstName] = @Original_FirstName AND [LastName] = @Original_LastName AND [Title]=@Original_Title AND [Phone]=@Original_Phone AND [AcctType]=@Original_AcctType AND [AcctStatus]= @Original_AcctStatus AND [AgencyUniqueId] = @Original_AgencyUniqueIdAND [UserId] = @Original_UserId);IF @@ROWCOUNT = 0BEGINSET @err_message = 'Data has been edited by another user since you began viewing this information.'RAISERROR (@err_message,11, 1)ROLLBACK TRANSACTIONRETURNEND IF @@ERROR <> 0 BEGINROLLBACK TRANSACTIONRETURN ENDIF @blnPrimary = 1 BEGIN IF LOWER(@AcctStatus) <> LOWER(@AgencyAcctStatus)/*** Update Users Acct. Status ***//* update all users in same agency profile */UPDATE [cdds_User_Profile] SET [AcctStatus] = @AcctStatus,[LastUpdatedDate] = GETDATE() WHERE ([AgencyUniqueId] = @Original_AgencyUniqueIdAND [UserId] = @Original_UserId); IF @@ROWCOUNT = 0BEGINSET @err_message = 'Data for this agency has been edited by another user since you began viewing this information.'RAISERROR (@err_message,11, 1)ROLLBACK TRANSACTIONRETURNENDIF @@ERROR <> 0 BEGINROLLBACK TRANSACTIONRETURN ENDEND</pre><pre>
View 6 Replies
View Related
Sep 5, 2000
Hi,
I am trying to do selective updates for rows where a column matches a column in another table. I want to do something like this, only 'this' does not work, and nothing else I could think of (I tried joins also) worked. What am I missing? I hope this explanation makes sense.
UPDATE queryresultsmodel SET queryresultsmodel.tableforcedoutdate = getdate()
Where Exists (Select tablename from queryresultsmodel q inner join orphanul o on q.tablename = o.name)
Thanks for any help,
Judith
View 1 Replies
View Related
Jul 27, 1998
Hi,
Has anyone had any problems on one row updates on a table where you have defined horizontal and vertical partitioning of the data to be replicated?
When I execute an update clause that modifies just one row the log reader misses the modification and it does not get replicated to the other databases.
If I do the same update clause but on several rows then all the modifications are read by the log reader and the replication task goes ok.
What might be wrong?
-janne
View 1 Replies
View Related
Nov 1, 2006
Hello!
I have 2 database (DB1 and DB2) in 1 server. I want to reflect new changes made in DB1.tbl_pm_project to DB2.tbl_pm_project. Any idea on how to do it.
Thank you
JJ-hon
View 3 Replies
View Related
Jun 23, 2006
Hi,I have a table with 20.000.000 of tuples.I have been monitoring the performance of the insertion and updates,but not convince me at all.The table have 30 columns, what and 12 of it, are calcultated column.The test that i do was this:1 Insertion with all the columns and calculing the calcultated columnsin the insertion sentence.1 insertion and all the columns calculated in @vars..1 insertion with the basic fields, and 10 updates.And the result was that the last test was the most performant.What is your opinion?
View 4 Replies
View Related
Feb 11, 2008
Hi
I'm using VS'08 and develop in VB.
I'm using SQL CE 3.5 as a local cache for SQL'05 tables.
The table that's being updated uses an interger autonumber for the PK.
When the program starts up and inserts records into the table, it works, as long as no on else is insering recoreds into the same table.
Once a duplicate PK is created by another WS, the records no longer update the SQL'05 table.
If the blocked program is restarted, it'll insert records with a PK that's past the one found at the initial sync. until blocked again.
PK ---- ProgID ----- MSG
1
0
4
B
hello
2/6/2008 9:33:55 PM
2/6/2008 9:33:55 PM
2
0
4
B
hello
2/11/2008 7:54:38 PM
2/11/2008 7:54:38 PM
3
1
1
B
hello
2/11/2008 8:32:41 PM
2/11/2008 8:32:41 PM
4
0
4
T
just something
2/11/2008 8:34:18 PM
2/11/2008 8:34:18 PM
5
1
1
B
one
2/11/2008 9:13:41 PM
2/11/2008 9:13:41 PM
6
1
1
B
two
2/11/2008 9:14:06 PM
2/11/2008 9:14:06 PM
7
1
1
B
three
2/11/2008 9:14:35 PM
2/11/2008 9:14:35 PM
8
1
1
B
four
2/11/2008 9:15:04 PM
2/11/2008 9:15:04 PM
9
1
1
B
five
2/11/2008 9:15:59 PM
2/11/2008 9:15:59 PM
10
0
4
B
cp 1
2/11/2008 9:17:44 PM
2/11/2008 9:17:44 PM
11
0
4
B
cp 2
2/11/2008 9:18:13 PM
2/11/2008 9:18:13 PM
12
1
1
B
eight
2/11/2008 9:21:31 PM
2/11/2008 9:21:31 PM
13
0
4
B
cp 3
2/11/2008 9:21:52 PM
2/11/2008 9:21:52 PM
NULL
NULL
NULL
NULL
NULL
NULL
NULL
David L.
View 8 Replies
View Related
Feb 5, 2008
Hi. We're using SQL Server 2005 Express Edition for maintaining a relational DB for our soon-to-be released .NET app. The problem is that we expect that the definitions of the tables in the DB to occasionally change over time as we make updates to the software. Thus, during the installation of a software upgrade, we expect to run an SQL script that grabs the data from a table in its "old" format, re-structures the table, and then deposits the data back in the updated table. This seems to require some sort of version stamp on the table definition.
My main question is: What is the conventional way for handling versioning of table definitions?
Another question is: Is there a preferred procedure for handling the updating of the data during the installation of a software update?
Thanks.
Dave
View 3 Replies
View Related
Nov 6, 2007
Simple question:
I have two servers S1 and S2. Inmediately after new data on S1 is available I want to perform some actions on S2.
I can use a trigger on S1, but if S2 is down the transaction on S1 will be lost. I could use database replication but I only need one single table in S1 to report changes to S2
Is there any other approach I could use?
Thanks.
View 1 Replies
View Related
Apr 21, 2004
Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.
UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20
Error I am getting is :
Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
What is the best possible way to make it work? Thank you.
View 7 Replies
View Related
Nov 5, 2007
I am looking for pros and cons for the following scenarios:
When a table contains a unique key constraint is it viable to always do an insert and immediately check the @@ERROR value and if @@ERROR states a duplicate key exception then perform an update statement?
Another possible solution would be to always check if the key exists and then do the insert / update based upon that result. This method will always require two steps.
View 4 Replies
View Related
Dec 28, 2007
I was able to catch one update but not multiple updates or batch updates done to the table. I know the updated records are residing in inserted and deleted tables. Without using cursors, how can i read and compare all the rows in these two tables?
Following is the table structure:
Customer_Master(custmastercode, customer_company_name,updated_by)
Following is the trigger:
ALTER TRIGGER [TR_UPDATE_CUST]
ON [dbo].[CUSTOMER_MASTER]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT * FROM inserted)
BEGIN
declare @custcode int
Declare @message varchar(5000)
Declare @custommessage varchar(2000)
Declare @CUSTMASTERCODE int
Declare @CUSTOMER_COMPANY_NAME varchar(50)
Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '
select @custcode = [CUSTMASTERCODE],@UPDATED_BY = [UPDATED_BY] from inserted
Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '
IF(update([CUSTOMER_COMPANY_NAME]))
Begin
select @UCUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from deleted
select @CUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from inserted
Set @custommessage = 'Customer company name changed from ' + @UCUSTOMER_COMPANY_NAME + ' to ' + @CUSTOMER_COMPANY_NAME + '.'
Set @message = @message + @custommessage
End
Set @message = @message + ' Updated by ' + @UPDATED_BY + ' at ' + CAST(getdate() AS VARCHAR(20))+ '.'
INSERT INTO [CHANGE_HISTORY]
([CUSTMASTERCODE]
,[CHANGE_DETAILS])
VALUES (@custcode, @message)
END
END
View 7 Replies
View Related
Dec 5, 2007
Just curious if anyone has any in depth knowledge of how table join filtering works:
SELECT col1
FROM tbl1 a
INNER JOIN tbl2 b
ON a.col2 = b.col2
AND a.col3 = 1
(versus)
SELECT col1
FROM tbl1 a
INNER JOIN tbl2 b
ON a.col2 = b.col2
WHERE a.col3 = 1
Running some simple tests, the executions plans look identical. Does anyone know if/when either of these options would be preferential over the other? This is a rather difficult topic to Google and find any decent information.
Thanks!
View 5 Replies
View Related
Apr 10, 2008
I was looking at some sample queries on how to do product promotion. Basically im after the top few products that have been ordered along with a given product ID. I think this should do it, but wasnt sure about the 2nd join...and why ?
SELECT
ProductID,
ProductName,
SUBSTRING(Description, 1, 150) + '...'
AS Description
FROM Products
WHERE ProductID IN
(
SELECT TOP 5 details2.ProductID
FROM OrderDetails details1
INNER JOIN OrderDetails details2
ON details1.OrderID = details2.OrderID
WHERE details1.ProductID = @ProductID
AND details2.ProductID != @ProductID
GROUP BY details2.ProductID
)
View 2 Replies
View Related
May 16, 2008
I have 3 tables, students (studentId, studentName), classes (classId, className) and studentclass (studentId, classId, sessionId)
Want to display all studentnames, classnames and sessionIds, regardless of whether the student is enrolled in a specific class or not.
So the result set should show all students and all classes:
studentname classname sessionId
----------- --------- ---------
john english 2
john math <null>
jane english <null>
jane math 3
Please help in how to write such SQL?
Thanks in advance
View 6 Replies
View Related
Jul 20, 2005
I'm having two general problems trying to do a JOIN. I have a table withthree fields {Code, Date, Amount}. Code+Date is a unique key. I'm tryingto get a rowset with 1) one row for each unique Code+Date pair, 2) andwith each row containing, {Code, Amount for Date-A and Amount forDate-B}. Basically, I want to create two temp tables with the Amounts fora specified Date and then Join them.The problems are1) I'm trying to do this in SQL-Server 7 with a single stantment, and2) If a Code+Date pair doesn't have any Amounts, I'd still like a rowreturned with NULLs.Anybody have any wisdom on this??Thanks
View 9 Replies
View Related
Jul 20, 2005
I got this problem where I need to look up values in two columns fromanother table.I can get OUTER LEFT JOIN working when looking up one column from a tablebut when looking up at two columns from a table I can't get the SQL syntaxcorrect.The scenario isA table has definitions for abbreviation of initials. From my search querythere are two columns of initials, I am having problems in writing an SQLstatement to look up and replace both columns of initials with theircorrect definitions. I was using an OUTER LEFT JOIN statementI am sorry if this isnt the correct Newsgroup to post this.
View 3 Replies
View Related
Mar 5, 2014
Facing deadlock issues in my ETL job .
The driver table , which keeps track of what datamarts ran and for what date range gets updated frequently during the etl run . There can be as many as 250 updates issued on this table in a single second.
Now this table is a heap , and there are no indexes on it .
During these updates , we encounter deadlocks causing the ETL job to fail .
Will adding an index faciltate?
View 4 Replies
View Related
Jun 27, 2014
I am basically trying to update a table which reflects account transactions. Accounts get paid in full but occasionally balance payments can be reversed and I want to update the table to show this - I need to show which period the account was previously paid in full.I've created a simplified version of the scenario and below are a couple of examples of things I've tried that do not work. I understand why they do not work but I'm struggling to figure out how to update the 'PeriodPrevPaidInFull' field.
create table Trans
(
AccNo int,
Transaction_Period_Index int,
PeriodOpeningBalance money,
DebtBalance money,
PeriodPaidInFull int NULL,
PeriodPrevPaidInFull int NULL,
[code]...
View 9 Replies
View Related
Mar 5, 2015
I'm trying to do something like this:
Loop through #Temp_1
-Execute Sproc_ABC passing in #Temp_1.Field_TUV as parameter
-Store result set of Sproc_ABC into #Temp_2
-Update #Temp_1 SET #Temp_1.Field_XYZ= #Temp_2.Field_XYZ
End Loop
It appears scary from a performance standpoint, but I'm not sure there's a way around it. I have little experience with loops and cursors in SQL. What would such code look like? And is there a preferable way (assuming I have to call Sproc_ABC using Field_TUV to get the new value for Field_XYZ?
View 2 Replies
View Related
Jul 20, 2005
Hopefully someone can at least point me in the right direction for moreresearch (e.g.: correct terminology). My only previous experience was justdumping data into a database using ODBC, and that was some years ago so nowmostly forgotten.I need to write an NT Service/Application (in C/C++) that will be gettingdata sent to it via SQL Server 2000. The data will arrive in my SQL Server(read-only access), via replication of tables from another remote SQLServer.My application needs know when new row are inserted, or updated so it can toread this data (needs to be quick/timely so hopefully no polling) to theninterface with other remote proprietary systems.T.I.A.PS: If you can recommend appropriate books on SQL Server 2000 that wouldalso be useful.
View 2 Replies
View Related
Jul 20, 2005
Hello,Can someone point me to getting the total number of inserts and updates on a tableover a period of time?I just want to measure the insert and update activity on the tables.Thanks.- Vish
View 3 Replies
View Related
Sep 10, 2007
Hi all,,
I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..
May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..
I have a flat file that I want to merge with table in SQL server 2005.
1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).
Now here is my question.
I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).
I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..
Here is an example:
Table A
--------------
1 test test1 test2 test3 test4 test5
2 test test6 test7 test8 test9 test10
Table X
------------
1 test test1 test2 test99 test4 test5
2 test test98 test97 test 96 test95 test94
--------------------------------------------------------
Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..
The new Table A should look like
NEW Table A'
-----------------
test test1 test2 test3 test4 test5
test test6 test7 test8 test9 test10
test test98 test97 test 96 test95 test94
------------------------------------
I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?
Thanks in advance.
View 6 Replies
View Related
Oct 29, 2006
I'm trying, with little success, to achieve something that should be quite easy (I think!) and any advice would be appreciated.
I have a leagues table structured so:
LeagueID | Name | Player1 | Player 2 ... Player6
and the data in the player columns is a userid from the users table and I'm trying to display the Leagues but with the player names rather than player IDs.
I'm working along the lines of
Code:
select
u1.displayname as Player1,
u2.displayname as Player2
from DCMLeagues as L
inner join Users as u1 on L.player1 = u1.userid
inner join Users as u2 on L.player2 = u2.userid
but with little success so far. Any thoughts would be appreciated! Thanks very much in advance.
-- Chris
View 5 Replies
View Related
Apr 25, 2008
Hello team.
I have an issue I'm hoping you can help me with.
I have very large sql server with 15 databases and thousands of tables. We have an "employee" table where we have historically been joining to the "EmployeeName" field in procs, views, etc.(bad practice, I know). I would like to now go back and make things right. I would like to identify ALL procs and views that have a join on the "EmployeeName" field, and modify it to use the "employeeid" field. I would like a script or a suggestion that would help me identify all the places where I would need to make this change.
I thought of querying the syscomments table, but the joins are not always laid out the same way so I know I wouldn't be able to catch all of them. Maybe using profiler to capture all statements executed and have them trigger an email to a DB developer every time? I don't know... Suggestions?
Many Thanks
View 2 Replies
View Related
Aug 6, 2007
Hi I'm new to SQL and I'm having some problems with the following join. I have a table, Ticket, with two int fields, Submitter and Acceptor, among many others. These fields reference the ID of users stored in the Users table and are usually set to different IDs (ie. they are independant fields).
Here is the relevent SQL to get the ticket records (generated by the Query Editor in SQL Management Studio Express):
SELECT Users.Name, Tickets.ID
FROM Tickets INNER JOIN
Users ON Tickets.Submitter = Users.ID AND Tickets.Acceptor = Users.ID
Problem is, this only returns records where the submitter and acceptor IDs are the same which is rarely the case. What am I doing wrong here? I've created a lot of joins for other records without problem - this is the only case where two fields are coming from the same table.
Thanks for any help.
View 2 Replies
View Related
May 4, 2006
Hello all,Can someone help me with this SQL?1) EMPLOYEE table has columns 'employee_id' and 'emp_sid' and othercolumns2) EMPLOYEE_BENEFITS table has a column called employee_entity, thiscolumn can be joined to either 'employee_id' OR 'emp_sid' but not bothin the EMPLOYEE table.3) EMPLOYEE_TRACK table has column called employee_track_entity, thiscolumn can be joined to the employee_benefits_id (PK) of theEMPLOYEE_BENEFITS table.I am listing the sql for the tables (the tables shows only the columnsin question)CREATE TABLE [dbo].[EMPLOYEE] ([employee_id] [int] IDENTITY (1, 1) NOT NULL ,[empsid_id] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[EMPLOYEE_BENEFITS] ([employee_benefits_id] [int] IDENTITY (1, 1) NOT NULL ,[employee_entity] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[EMPLOYEE_TRACK ] ([employee_track_id] [int] IDENTITY (1, 1) NOT NULL ,[employee_track_entity] [int] NOT NULL) ON [PRIMARY]GOSELECT * FROM EMPLOYEE eINNER JOINEMPLOYEE_BENEFITS eb ON (e.employee_id = eb.employee_entity ORe.empsid_id = eb.employee_entity)INNER JOINEMPLOYEE_TRACK et ON eb.employee_benefits_id = et.employee_track_entityThe above SQL I wrote is this: the second inner join uses a OR to joineither of the columns in the first table EMPLOYEE. There is performancedegradation with this SQL. With huge data It takes about 30 seconds toexecute. I know this is not the perfect way to do it, can anyone of theSQL Gurus please enlighten me to a faster approach?If I dont use the OR I can try left join on the same tableEMPLOYEE_BENEFITS twice by changing the join types, but If I did thiswhat table alias can I use to join to the 3rd table?SELECT * FROM EMPLOYEE eLEFT JOINEMPLOYEE_BENEFITS eb1 ON e.employee_id = eb.employee_entityLEFT JOINEMPLOYEE_BENEFITS eb2 ON e.empsid_id = eb.employee_entityINNER JOINEMPLOYEE_TRACK et ON [???].employee_benefits_id =et.employee_track_entitythanksadi[Sorry I am posting this twice, on SQL Programming forum too]
View 3 Replies
View Related
Jan 5, 2006
I'm new to SQL 2005 & C# - I'm a MySQL/PHP crossover.
I'm using s Stored Procedure and I'm trying to do multiple joins onto
one table. I have 6 fields in one table that are foreign keys of
another table:
Table1
---------
id
PrimaryCode
SecondaryCode1
SecondaryCode2
SecondaryCode3
SecondaryCode4
SecondaryCode5
Table 2
---------
id
Title
CommCode
The fields in table 1 (except is obviously) hold the id of a row in
Table 2. When displaying data I want to display "Title" -
"CommCode" for each item in Table 1. I got myself started by
searchig on the net and I have a stored procedure. The obvious
problem is that as it goes through the Query only the last value
remains in place - since each value before it is cleared in the
UNION. How can I do this?? Here's my Stored Procedure:
=====================================
ALTER PROCEDURE GetRegistersSpecific
@SearchTxt int
AS
SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
PrimaryCode AS MyID, Title, CommodityCode
FROM registrations
JOIN CommodityCodes ON PrimaryCode = CommodityCodes.id
UNION
SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode1 AS MyID, Title, CommodityCode
FROM registrations
JOIN CommodityCodes ON SecondaryCode1 = CommodityCodes.id
UNION
SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode2 AS MyID, Title, CommodityCode
FROM registrations
JOIN CommodityCodes ON SecondaryCode2 = CommodityCodes.id
UNION
SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode3 AS MyID, Title, CommodityCode
FROM registrations
JOIN CommodityCodes ON SecondaryCode3 = CommodityCodes.id
UNION
SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode4 AS MyID, Title, CommodityCode
FROM registrations
JOIN CommodityCodes ON SecondaryCode4 = CommodityCodes.id
UNION
SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode5 AS MyID, Title, CommodityCode
FROM registrations
JOIN CommodityCodes ON SecondaryCode5 = CommodityCodes.id
WHERE registrations.ID = @SearchTxt
=====================================
Thanks
View 4 Replies
View Related
Nov 9, 2015
I have created a package that will insert new rows into destination1 if the AnID does not exist in Source1.
This uses a data flow task which contains a oledb source, lookup and oledb destination.
Source1
Field Name AnID Acol1 Acol2 Destination1 Field Name AnID Acol1 Acol2
I want to be able to update the destination1. Acol1 and destination1. Acol2 if the Source1.Acol1 or Source1.Acol2 have changed.
To do this, would I need to create separate data flow task that includes a source, lookup and destination.
Or is this possible to build into my insert new records data flow task...
View 3 Replies
View Related
