Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

List value based on another field value - Query question Expand / Collapse
Author
Message
Posted Tuesday, June 3, 2014 8:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 PM
Points: 95, Visits: 603
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



Post #1577201
Posted Tuesday, June 3, 2014 10:07 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 710, Visits: 4,535
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.
Post #1577206
Posted Wednesday, June 4, 2014 5:49 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 196, Visits: 719
Could you use something like
COALESCE(email1, email2, 'No Email')

Post #1577272
Posted Wednesday, June 4, 2014 8:28 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 710, Visits: 4,535
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...
Post #1577366
Posted Wednesday, June 4, 2014 8:33 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 196, Visits: 719
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

Post #1577370
Posted Wednesday, June 4, 2014 8:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 3,374, Visits: 7,300
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




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1577378
Posted Wednesday, June 4, 2014 8:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 PM
Points: 95, Visits: 603
Thank you everyone for your input, I am going to test shortly and let you know ..


Post #1577379
Posted Wednesday, June 4, 2014 12:11 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 710, Visits: 4,535
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
Post #1577478
Posted Tuesday, June 24, 2014 9:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 PM
Points: 95, Visits: 603
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



Post #1585552
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse