June 21, 2010 at 7:59 am
Hi, I have been trying to work out a query where I do a join on the same table. I know how to do the join but I am not sure how to get the results Iām looking for.
I have a table called contact in that table, below is a sample with data. There thousands of rows in that table and the record types are determined by the field record_type. There are only learners and employers as record types in the database.
What I need to do is and update query that takes the employer record client_id and matches it to the learner client_ids and then updates those learner client_ids with the uniqueid of that employer.
Thanks for any help:
So the results from my example below will show:
Uniqueid company firstname lastname client_id record_type
uid11111 Company a John S 111 Employer
uid22222 Company a James S 111 Learner
uid33333 Company a Bob S 111 Learner
uid44444 Company a Tim S 111 Learner
The results I need it to show are:
Uniqueid company firstname lastname client_id record_type
uid11111 Company a John S 111 Employer
uid22222 Company a James S uid11111 Learner
uid33333 Company a Bob S uid11111 Learner
uid44444 Company a Tim S uid11111 Learner
/****** Object: Table [dbo].[contacts] ******/
CREATE TABLE [dbo].[contacts] (
[uniqueid] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[client_id] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[record_type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO contacts (uniqueid, company, firstname, lastname, client_id, record_type)VALUES ('uid11111','Company a','John','S','111','Employer')
INSERT INTO contacts (uniqueid, company, firstname, lastname, client_id, record_type)VALUES ('uid22222','Company b','James','T','111','Learner')
INSERT INTO contacts (uniqueid, company, firstname, lastname, client_id, record_type)VALUES ('uid33333','Company c','Bob','K','111','Learner')
INSERT INTO contacts (uniqueid, company, firstname, lastname, client_id, record_type)VALUES ('uid44444','Company d','Tim','Y','111','Learner')
June 21, 2010 at 8:22 am
I think I am a little confused. Currently in your example all the records have the same client id. based on your example you what the learners client ID to have two extra 11's added to the id?
where do the 2 extra 11's come from?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 21, 2010 at 8:56 am
Hi Dan,
I think I am a little confused. Currently in your example all the records have the same client id. based on your example you what the learners client ID to have two extra 11's added to the id?
where do the 2 extra 11's come from?
Yea, this is hard to explain as the data is held in a CRM system and i have give you a made up example. Ignore my unqueid values as in teh real database they a 16 chars alphanumeric values. Also the client_id's are all 4 alpha numeric characters.
In the real table there are 12000 rows many learners to one employer. I need each employers uniqueid inserted into each of the linked learners client_id where the employers client_id = the relevant learners client_id.
If i can see that one example work i should be able to get the update working for all rows in the db.
Thanks for looking.
June 21, 2010 at 9:03 am
If you are setting the learner id to the employer id where they are already equal then you are setiing ID abc to id abc. I have to be missing something. can you give us a different example? some more realistic data even though it will still be a sample might help me understand what youa re trying to do and what to fields you are trying to join on. Be patient with me it is monday.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 21, 2010 at 9:08 am
How does this work for you?
SELECT c1.uniqueid,
c1.company,
c1.firstname,
c1.lastname,
client_id = CASE WHEN c1.record_type = 'Employer' THEN c1.client_id
ELSE c2.uniqueid
END,
c1.record_type
FROM Contacts c1
JOIN Contacts c2
ON c1.client_id = c2.client_id
AND c2.record_type = 'Employer'
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 21, 2010 at 9:29 am
That's got it. Thanks very much for the help. I'll be keeping that script close to hand š Thanks again.
June 21, 2010 at 9:29 am
I'm sorry, I just noticed that you are asking for an UPDATE statement, and I provided a SELECT.
So, how's this:
UPDATE c1
SET client_id = CASE WHEN c1.record_type = 'Employer' THEN c1.client_id
ELSE c2.uniqueid
END
FROM Contacts c1
JOIN Contacts c2
ON c1.client_id = c2.client_id
AND c2.record_type = 'Employer'
SELECT * FROM Contacts
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 21, 2010 at 9:54 am
That's great. i was playign with the select to get the update but that's going to save me loads of time. Thats for the help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply