January 23, 2013 at 8:30 am
I need some help with a query that involves changing the joins when a field is a certain value. The problem I am trying to solve is in the second part of the union. If the deliverymethod is ‘Virtual’ then I need to return the shipping address if the address description is like ‘shipping’ else return the primary address. The joins change to the address table for these two conditions. The contact table is related to the address table in a one-to-one relationship based on addressid in each table (the primary address). However the address table can have multiple addresses (shipping, billing, etc.) for a contact joined on the contact table contactid to the address table entityid. Can someone tell me how to change this query? Any help is appreciated.
SELECT C.FIRSTNAME,C.LASTNAME,A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.POSTALCODE,A.COUNTRY,
C.WORKPHONE,C.EMAIL,EH.ENROLLSTATUS
FROM SYSDBA.EVXENROLLHX EH
INNER JOIN SYSDBA.EVXEVENT E ON EH.EVXEVENTID = E.EVXEVENTID
INNER JOIN SYSDBA.EVXEVTICKET ET ON EH.EVXEVTICKETID = ET.EVXEVTICKETID
INNER JOIN SYSDBA.CONTACT C ON ET.ATTENDEECONTACTID = C.CONTACTID
INNER JOIN SYSDBA.ADDRESS A ON C.ADDRESSID = A.ADDRESSID
WHERE E.DELIVERYMETHOD != 'Virtual'
and EH.EVXEVENTID = '?'
UNION
SELECT C.FIRSTNAME,C.LASTNAME,A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.POSTALCODE,A.COUNTRY,
C.WORKPHONE,C.EMAIL,EH.ENROLLSTATUS
FROM SYSDBA.EVXENROLLHX EH
INNER JOIN SYSDBA.EVXEVENT E ON EH.EVXEVENTID = E.EVXEVENTID
INNER JOIN SYSDBA.EVXEVTICKET ET ON EH.EVXEVTICKETID = ET.EVXEVTICKETID
INNER JOIN SYSDBA.CONTACT C ON ET.ATTENDEECONTACTID = C.CONTACTID
CASE
WHEN DESCRIPTION LIKE 'Shipping%' THEN INNER JOIN SYSDBA.ADDRESS A ON C.CONTACTID = A.ENTITYID
ELSE INNER JOIN SYSDBA.ADDRESS A ON C.ADDRESSID = A.ADDRESSID
END
WHERE E.DELIVERYMETHOD = 'Virtual'
and EH.EVXEVENTID = '?'
January 23, 2013 at 9:24 am
Pirate92 (1/23/2013)
SELECT C.FIRSTNAME,C.LASTNAME,A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.POSTALCODE,A.COUNTRY,C.WORKPHONE,C.EMAIL,EH.ENROLLSTATUS
FROM SYSDBA.EVXENROLLHX EH
INNER JOIN SYSDBA.EVXEVENT E ON EH.EVXEVENTID = E.EVXEVENTID
INNER JOIN SYSDBA.EVXEVTICKET ET ON EH.EVXEVTICKETID = ET.EVXEVTICKETID
INNER JOIN SYSDBA.CONTACT C ON ET.ATTENDEECONTACTID = C.CONTACTID
CASE
WHEN DESCRIPTION LIKE 'Shipping%' THEN INNER JOIN SYSDBA.ADDRESS A ON C.CONTACTID = A.ENTITYID
ELSE INNER JOIN SYSDBA.ADDRESS A ON C.ADDRESSID = A.ADDRESSID
END
WHERE E.DELIVERYMETHOD = 'Virtual'
and EH.EVXEVENTID = '?'
Just join both tables and use the case in the select, like this.
SELECT
C.FIRSTNAME,C.LASTNAME,
ADDRESS1 = CASE WHEN A.DESCRIPTION LIKE 'Shipping%' THEN A1.ADDRESS1 ELSE A2.ADDRESS1 END,
ADDRESS2 = CASE WHEN A.DESCRIPTION LIKE 'Shipping%' THEN A1.ADDRESS2 ELSE A2.ADDRESS2 END,
CITY = CASE WHEN A.DESCRIPTION LIKE 'Shipping%' THEN A1.CITY ELSE A2.CITY END,
STATE = CASE WHEN A.DESCRIPTION LIKE 'Shipping%' THEN A1.STATE ELSE A2.STATE END,
POSTALCODE = CASE WHEN A.DESCRIPTION LIKE 'Shipping%' THEN A1.POSTALCODE ELSE A2.POSTALCODE END,
COUNTRY = CASE WHEN A.DESCRIPTION LIKE 'Shipping%' THEN A1.COUNTRY ELSE A2.COUNTRY END,
C.WORKPHONE,C.EMAIL,EH.ENROLLSTATUS
FROM SYSDBA.EVXENROLLHX EH
INNER JOIN SYSDBA.EVXEVENT E ON EH.EVXEVENTID = E.EVXEVENTID
INNER JOIN SYSDBA.EVXEVTICKET ET ON EH.EVXEVTICKETID = ET.EVXEVTICKETID
INNER JOIN SYSDBA.CONTACT C ON ET.ATTENDEECONTACTID = C.CONTACTID
LEFT JOIN SYSDBA.ADDRESS A1 ON C.CONTACTID = A1.ENTITYID
LEFT JOIN SYSDBA.ADDRESS A2 ON C.ADDRESSID = A2.ADDRESSID
CASE
WHEN DESCRIPTION LIKE 'Shipping%' THEN INNER JOIN SYSDBA.ADDRESS A ON C.CONTACTID = A.ENTITYID
ELSE INNER JOIN SYSDBA.ADDRESS A ON C.ADDRESSID = A.ADDRESSID
END
WHERE E.DELIVERYMETHOD = 'Virtual'
and EH.EVXEVENTID = '?'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply