SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


List value based on another field value - Query question


List value based on another field value - Query question

Author
Message
lsalih
lsalih
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1437 Visits: 1059
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



pietlinden
pietlinden
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13928 Visits: 14121
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.
Dana
Dana
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5890 Visits: 3450
Could you use something like
COALESCE(email1, email2, 'No Email')


pietlinden
pietlinden
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13928 Visits: 14121
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...
Dana
Dana
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5890 Visits: 3450
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


Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41568 Visits: 19815
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
lsalih
lsalih
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1437 Visits: 1059
Thank you everyone for your input, I am going to test shortly and let you know ..



pietlinden
pietlinden
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13928 Visits: 14121
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
lsalih
lsalih
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1437 Visits: 1059
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search