June 3, 2014 at 8:59 pm
I have employee table and its contact info. I need to list basic information about employee such as name, and primary email contact. The contact table has orderby field, if 1 it means the email listed is primary, and if value is 2 means the email is secondary. So for each employee, there is two corresponding email listed in contact table based on orderby field you can tell if the email listed is primary or secondary. Initially I wrote below to extract the email to list employee primary email, but later I realized that some records with orderby =1 does not have email, but there is an email value when orderby =2. I would like to modify my query to return email for the employee's primary contact, if null, then get the secondary email value, else just type no email listed. Can you please help me with writing this query?
example of contact table data
empid email orderby
1 t@t.com 1
1 s@s.com 2
2 q@.com 1
2 q@.com 2
3 z@te.com 1
3 Z@e.com 2
SELECT EMP.EMPNO
, PER.LNAME
, PER.FNAME
, plist.EMAIL
,PER.Gender
from
Employee EMP inner join
PERSON PER on PER.PERID = EMP.EMPID
inner join ( select Cont.email , EMP.EMPNO from contact cont
inner JOIN PERSON PER par ON par.PERID = cont.PERID and cont.ORDERBY = 1
inner JOIN Employee EMP ON EMP.EMPID = cont.EMPID) plist on plist.EMPNO = EMP.EMPNO
June 3, 2014 at 10:07 pm
This seems to work... If you post consumable data (create table scripts, and inserts), it's a lot easier for people to help you - since you've set up the problem for them. Then they can just bang out a solution easily. Like you see below. (you could copy the whole thing into a query window and run it and set up the problem). Makes helping you much easier.
use tempdb;
go
create table Contact(
empID int,
email varchar(30),
seq tinyint);
go
insert into Contact(empID,email,seq)
values (1,'t@t.com',1),(1, 's@s.com', 2),
(2, 'q@.com', 1),
(2, 'q@.com', 2),
(3, 'z@te.com', 1),
(3, 'Z@e.com', 2);
insert into Contact(empID,email,seq)
values (4,'b@te.com',2);
insert into Contact(empID,email,seq)
values (5, null,1),(5,'fifth@gin.com',2);
SELECT e1.EmpID, seq, email
FROM
-- get the first non-null sequence # where the email is not empty
(SELECT EmpID, MIN(seq) As MinSeq
FROM contact
WHERE email IS NOT NULL
GROUP BY EmpID) e1
INNER JOIN
-- join back to another copy of the table and retrieve what we want
(SELECT EmpID, seq, email
FROM contact) e2
-- use the join to do the filtering...
ON e1.empid=e2.empid
AND e1.minseq = e2.seq;
There's probably a neater way of doing this, but it's late and my brain is fried... but it works.
June 4, 2014 at 5:49 am
Could you use something like COALESCE(email1, email2, 'No Email')
June 4, 2014 at 8:28 am
That's what I was thinking at first, but the two values are not in the same record. That's why there's all the weird coding...
June 4, 2014 at 8:33 am
How about:
select e.empid, coalesce(c1.email, c2.email, 'No email')
from employee e
left join contact c1 on e.empid = c1.empid AND c1.orderby = 1
left join contact c2 on e.empid = c2.empid AND c2.orderby = 2
June 4, 2014 at 8:56 am
Or maybe something like this:
CREATE TABLE Employee(
EMPID int,
FNAME varchar(100),
LNAME varchar(100))
CREATE TABLE Contact(
EMPID int,
EMAIL varchar(100),
ORDERBY tinyint)
INSERT INTO Employee
VALUES(1, 'John', 'Smith'),
(2, 'Mary', 'Jones'),
(3, 'Peter', 'Parker')
INSERT INTO Contact
VALUES(1, 't@t.com', 1)
,(1, 's@s.com', 2)
--,(2, 'q@.com', 1)
,(2, 'q@.com', 2)
--,(3, 'z@te.com', 1)
--,(3, 'Z@e.com', 2);
SELECT e.EMPID,
e.FNAME,
e.LNAME,
ISNULL( c.EMAIL, 'No Email') EMAIL
FROM Employee e
OUTER
APPLY (SELECT TOP 1 EMAIL
FROM Contact c
WHERE c.EMPID = e.EMPID
ORDER BY ORDERBY) c;
GO
DROP TABLE Employee
DROP TABLE Contact
June 4, 2014 at 8:58 am
Thank you everyone for your input, I am going to test shortly and let you know ..
June 4, 2014 at 12:11 pm
Luis,
Outer apply... I should have figured. Still trying to get my head around that one... I mean, I know what it does, but remembering to use it... I knew that there had to be an easier way, I just wasn't sure what it was at midnight...
cool example! Thanks
June 24, 2014 at 9:00 am
Luis -
I completely forgot to update the post, thank you for your great solution using outer apply. Your answer helped me fix my query. I highly appreciate it.
Thanks again.
Lava
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply