CODEWITHSUNDEEP
sqlsql-server
Merlin
Merlin

Reputation: 87

SQL Conditional Field Retrieval

I have a situation where I am attempting to pull member phone numbers back for those who have appointments for the next day. The issue is the manner in which data is stored in the database. The MEMBERS table has four fields: phone1, phone2, phone3, phone4, with four additional fields of phone1label, phone2label, phone3label, and phone4label. In the User Interface, the user can specific what eat phone number is from a drop-down with 4 being mobile. Therefore, is there a way to only pull back the field for each MEMBER record where the phone#label = '4'? For example, John Doe might have his mobile number in phone1 with phone1label = '4' and John Q Public might have his mobile number in field phone2 with phone2label = '4'.

Then I need to add this logic to the following query:

SELECT SCSERVICES.servicecode,
       SCSERVICES.servicename,
       SCSESSIONS.scheduleid,
       SCSERVICES.servicetype,
       SCSERVICECATEGORIES.servicecategory,
       SCSCHEDULES.scheduledatefrom,
       MEMBERS.lname,
       MEMBERS.fname,
       MEMBERS.phone1,
       MEMBERS.phone2,
       MEMBERS.phone3,
       MEMBERS.phone4,
       MEMBERS.phone1label,
       MEMBERS.phone2label,
       MEMBERS.phone3label,
       MEMBERS.phone4label,
       SCSCHEDULES.timestart,
       SCSCHEDULES.schedulestatus
FROM SCSESSIONS
INNER JOIN SCSERVICES ON SCSESSIONS.serviceid = SCSERVICES.serviceid
INNER JOIN SCSERVICECATEGORIES ON SCSERVICES.servicecategoryid = SCSERVICECATEGORIES.servicecategoryid
INNER JOIN SCSCHEDULES ON SCSESSIONS.scheduleid = SCSCHEDULES.scheduleid
INNER JOIN MEMBERS ON SCSCHEDULES.memid = MEMBERS.memid
WHERE (SCSERVICECATEGORIES.servicecategory = 'Spa')
  AND (SCSERVICES.siteid = '779')
  AND (CAST(SCSCHEDULES.scheduledatefrom AS DATE) = '04-27-2016')
  AND (SCSCHEDULES.schedulestatus = '1')

Upvotes: 1

Views: 58

Answers (1)

D Stanley
D Stanley

Reputation: 152576

You can use a CASE statement:

CASE WHEN phone1label = '4' THEN phone1
     WHEN phone2label = '4' THEN phone2
     WHEN phone3label = '4' THEN phone3
     WHEN phone4label = '4' THEN phone4
END AS MobilePhone

Note that it would be cleaner if the phone numbers were normalized into a separate table instead of having multiple fields per record.

Upvotes: 3

Related Questions