SQL that use 3 columns in the same table as input to query

[xstrom]

Hi,

SQL is not my main area of expertise so I need some help.

I have the following table (PhonePerson) which contains 5 columns:
PersonGUI (PK, Employee ID)
PhoneType (MOBILE,DAY,FAX ,HOME)
PhoneID (1=MOBILE, 2=DAY, 3=FAX, 4=HOME)
AreaCode (Area code, ex 415)
Phone (Phone #, without area code, ex 444-4444).

I need to generate a sql that checks to see if an employee has a value for 'MOBILE' and if it does it needs to return AreadCode+' '+Phone in a column called 'Phone' for that employee. If there is no value for 'MOBILE' for a certain employee it instead needs to return the value for 'DAY' by AreaCode+' ' +Phone in the 'Phone' column.

Any help is greatly appreciated!

Thanks!

[QVeen72]

Hi,

Not clearly understood your requirements..

Looks like you require 'AreaCode+' '+Phone " in both the cases...
simply query for that..
or else give an example with 3-4 records...

Regards
Veena

[xstrom]

Hi Veena,

Thanks for your quick reply. I think you understood it correctly, and yes, the answer is most likely very easy for an expert. This is an example of data in the PhonePerson table:


00001 1 MOBILE 954 444-4444
00001 2 DAY 954 555-5555
00002 2 DAY 954 666-6666


So for PersonGUI (Employee ID) 00001 I want the select statement to first check if there is a valid AreaCode and Phone for MOBILE. If there are, then it should return the value of AreaCode and Phone for MOBILE in a column called "Phone". For PersonGUI 00002 the select should again check to see if there is a valid AreaCode and Phone for MOBILE and in this case there is not, so I would like it to return the value for AreaCode and Phone for DAY instead in a custom formatted column called "Phone".


Thanks!

[QVeen72]

Hi,

You can try this :

Select PersonGUI , AreaCode & ' ' & Phone As Phone From PhonePerson Where PhoneID = 1
Union All
Select PersonGUI , AreaCode & ' ' & Phone As Phone From PhonePerson Where PhoneID = 2 And PersonGUI & '/' & CSTR(PhoneID) Not In (
Select PersonGUI & '/' & CSTR(PhoneID) From PhonePerson Where PhoneID = 1)

You can refine the query by checking for not null / blank etc...

Regards
Veena