sql server 2000 - Conditional Join on multiple fields in the same table -
i have scenario have 2 tables below in both tables, either person_id or organisation_id populated
table_1
email_id, person_id, organisation_id, email_address usage 1 null 12 o12@email.com workemail 2 12 null p12@email.com workemail 3 13 null p13@email.com workemail 4 14 null p14@email.com workemail 5 null 13 o13@email.com workemail 6 14 null p14_p@email.com personalemail 7 null 14 o14@email.com personalemail 8 13 null p13_2@email.com workemail table_2
registration_id, person_id, organisation_id, name, registration_date 1 null 12 org12 10/05/2013 2 12 null p12 10/05/2013 3 13 null p13 10/05/2013 4 14 null p14 10/05/2013 5 null 13 o13 10/05/2013 6 null 14 o14 10/05/2013 i need select statment give me workemail of each registration record; registration record has more 1 work email address first record should selected (e.g records email_id 3 , 8 in table 1):
registration_id, person_id, organisation_id, name, email address 1 null 12 org12 o12@email.com 2 12 null p12 p12@email.com 3 13 null p13 p13@email.com 4 14 null p14 p14@email.com 5 null 13 o13 o13@email.com 6 null 14 o14 null i have tried doing following not quite sure if efficient way; besides, doesnt quite give me need:
select t1.registration_id, t1.person_id, t1.organisation_id, t1.name, t2.email_address table2 t1 left join table1 on t2.person_id = t1.person_id or t2.organisation_id = t1.organisation_id
revised answer
/* setup */ create table table_1 ( email_id bigint not null --identity(1,1) , person_id bigint , organisation_id bigint , email_address nvarchar(256) not null , usage nvarchar(16) not null ) insert table_1 (email_id, person_id, organisation_id, email_address, usage) select 1 ,null ,12 ,'o12@email.com' ,'workemail' union select 2 ,12 ,null ,'p12@email.com' ,'workemail' union select 3 ,13 ,null ,'p13@email.com' ,'workemail' union select 4 ,14 ,null ,'p14@email.com' ,'workemail' union select 5 ,null ,13 ,'o13@email.com' ,'workemail' union select 6 ,14 ,null ,'p14_p@email.com' ,'personalemail' union select 7 ,null ,14 ,'o14@email.com' ,'personalemail' union select 8 ,13 ,null ,'p13_2@email.com' ,'workemail' create table table_2 ( registration_id bigint not null --identity(1,1) , person_id bigint , organisation_id bigint , name nvarchar(32) not null , registration_date date not null ) insert table_2 (registration_id, person_id, organisation_id, name, registration_date) select 1 ,null ,12 ,'org12' ,'10/05/2013' union select 2 ,12 ,null ,'p12' ,'10/05/2013' union select 3 ,13 ,null ,'p13' ,'10/05/2013' union select 4 ,14 ,null ,'p14' ,'10/05/2013' union select 5 ,null ,13 ,'o13' ,'10/05/2013' union select 6 ,null ,14 ,'o14' ,'10/05/2013' /* results */ select t2.registration_id, t2.person_id, t2.organisation_id, t2.name, t1.email_address table_2 t2 left outer join ( select person_id, organisation_id, email_address table_1 inner join ( select min(email_id) email_id table_1 usage = 'workemail' group person_id, organisation_id ) b on a.email_id = b.email_id ) t1 on t2.person_id = t1.person_id or t2.organisation_id = t1.organisation_id original answer
i think you're after:
select x.registration_id, x.person_id, x.organisation_id, x.name, x.email_address ( select t2.registration_id, t2.person_id, t2.organisation_id, t2.name, t1.email_address, t1.usage , row_number() on (partition t2.registration_id, t1.usage order t1.email_id) r table_2 t2 left join table_1 t1 on t2.person_id = t1.person_id or t2.organisation_id = t1.organisation_id ) x (x.r = 1 , x.usage = 'workemail') --limit first email address if there multiple work email matches same registration (table2) record or x.usage <> 'workemail' --if it's not work email, don't limit number
Comments
Post a Comment