September 6, 2007 at 1:02 pm
I'm working on an interface between our HR system and our Time Keeping system and have one final piece I can't figure out. I'm hoping someone here can help...
I'm processing many fields, but will use only 2 or 3 for the sake of clarity in my question.
I'm processing EMP_Number, phone_number and phone_type. Phone_type can be either HOME, CELL or it can be NULL. I want the HOME number if it is populated, then to check for a CELL number. If neither is populated, it will return the NULL value.
Is this something done in the WHERE clause? Do I need a UNION of some sort? I'm trying to do this with only one SQL statement.
Any help would be greatly appreciated!
Bob
September 6, 2007 at 1:38 pm
Is the phone data stored in a separate table ? Can you give DDL of the tables involved ?
September 6, 2007 at 2:28 pm
In my "real" statement, there are 7-8 tables involved, but I am testing this in only one table. If I can get that to work, I can extrapolate the logic into my big statement.
In my example I have three columns: Name, Phone type and Phone number. Name is not nullable but the other two are.
The data in my sample table is as follows:
EMPLID PhoneType PhoneNumber
---------- ---------- ---------------
Bob CELL 270/860-1234
Bob HOME 270/821-5678
Tim CELL 270/860-7890
Paul NULL NULL
Andrew HOME 270/222-1212
Bob Businness 821/962-5112
My goal is to have each employee listed only once. If there is a HOME number, that should be returned. If no HOME number, the CELL (if it exists) should be returned. If they have neither, then NULL should be returned.
I have come up with something that works but I'm not sure if it is good SQL or not. It uses a UNION which I tried to avoid, but I can't figure another way out. Here is what I've got ...
SELECT
A.EMPLID,
A.PhoneType,
A.PhoneNumber
FROM tblPhoneNumbers A
WHERE A.PhoneType = 'CELL'
AND A.EMPLID NOT IN (SELECT EMPLID FROM tblPhoneNumbers where PhoneType = 'HOME')
union
SELECT
b.EMPLID,
b.PhoneType,
b.PhoneNumber
FROM tblPhoneNumbers b
WHERE b.PhoneType = 'HOME' or b.PhoneType is null
AND b.EMPLID NOT IN (SELECT EMPLID FROM tblPhoneNumbers where PhoneType = 'CELL')
which returns...
EMPLID PhoneType PhoneNumber
---------- ---------- ---------------
Andrew HOME 270/222-1212
Bob HOME 270/821-5678
Paul NULL NULL
Tim CELL 270/860-7890
(4 row(s) affected)
September 6, 2007 at 3:12 pm
Do a join to one of your main tables that has EmplID
SELECT a.EmplID,
COALESCE(C.PhoneType, H.PhoneType,'') PhoneType,
COALESCE(C.PhoneNumber, H.PhoneNumber,'') PhoneNumber
FROM MainTable a
LEFT OUTER JOIN tblPhoneNumbers C ON a.EmplID = C.EmplID AND C.PhoneType = 'CELL'
LEFT OUTER JOIN tblPhoneNumbers H ON a.EmplID = H.EmplID AND H.PhoneType = 'HOME'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply