I have a scenario where you have two tables as below where in both tables, either the person_id or organisation_id is 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 a select statment that will give me the workemail of each registration record; where the registration record has more than one work email address then the first record should be selected (e.g records with email_id 3 and 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 the following but not quite sure if this is the most efficient way; besides, it doesnt quite give me what I need:
SELECT t1.registration_id, t1.person_id, t1.organisation_id, t1.name, t2.email_Address
FROM 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'
/* get the results */
SELECT t2.registration_id, t2.person_id, t2.organisation_id, t2.name, t1.email_Address
FROM table_2 t2
left outer join
(
select person_id, organisation_id, email_address
from Table_1 a
inner join
(
select MIN(email_id) email_id
from Table_1
where Usage = 'WorkEmail'
group by 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 this is what you're after:
select x.registration_id, x.person_id, x.organisation_id, x.name, x.email_Address
from
(
SELECT t2.registration_id, t2.person_id, t2.organisation_id, t2.name, t1.email_Address, t1.usage
, row_number() over (partition by t2.registration_id, t1.usage order by t1.email_id) r
FROM table_2 t2
LEFT JOIN table_1 t1
ON t2.person_id = t1.person_id
OR t2.organisation_id = t1.organisation_id
) x
where (x.r = 1 and x.usage = 'WorkEmail') --limit to the first email address if there are multiple work email matches for the same registration (table2) record
or x.usage <> 'WorkEmail' --if it's not work email, don't limit the number