I am having an issue trying to form the proper SQL query for the job here. I have two tables, one is called CUSTOMER and the other is called CUSTOMER_CONTACT. To simplify this, I will only include the relevant column names.

• CUSTOMER columns: ID, CUSTOMERNAME

• CUSTOMER_CONTACT columns: ID, CUSTOMER_ID, CONTACT_VC, EMAIL

CUSTOMER_ID is the foreign key to link to the CUSTOMER table from CUSTOMER_CONTACT. CONTACT_VC is just the entry number for their contact information. There could be multiple CUSTOMER_CONTACT records for each customer, but they will have a unique CONTACT_VC. EMAIL can be null/blank on some or all as well.

I need to select the first CUSTOMER_CONTACT entry where EMAIL is NOT NULL/blank but if none of the CUSTOMER_CONTACT entries have an email address, then select CUSTOMER_CONTACT WHERE CONTACT_VC = 1

Any suggestions on how to accomplish this?

CodePudding user response：

The following approach uses ROW_NUMBER to retrieve a number based on your ordering logic within each CUSTOMER_ID group, then filters by the first record retrieved.

You may try the following:

SELECT 
    * 
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY CUSTOMER_ID
            ORDER BY (CASE WHEN EMAIL IS NOT NULL THEN 0 ELSE 1 END),CONTACT_VC 
        ) as rn
    FROM
        CUSTOMER_CONTACT
) t
WHERE rn=1

If you would like to join this to the customer table you may use the above query as a subquery eg

SELECT
   c.*,
   contact.*
FROM
   CUSTOMER c 
INNER JOIN (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY CUSTOMER_ID
            ORDER BY (CASE WHEN EMAIL IS NOT NULL THEN 0 ELSE 1 END),CONTACT_VC 
        ) as rn
    FROM
        CUSTOMER_CONTACT
) contact ON c.ID = contact.CUSTOMER_ID and contact.rn=1

CodePudding user response：

You can use APPLY:

SELECT *
FROM customer
OUTER APPLY (
    SELECT TOP 1 *
    FROM customer_contact
    WHERE customer_contact.customer_id = customer.id AND (
        customer_contact.email IS NOT NULL OR
        customer_contact.contact_vc = 1
    )
    ORDER BY CASE
        WHEN customer_contact.email IS NOT NULL THEN 1,
        WHEN customer_contact.contact_vc = 1 THEN 2
    END, customer_contact.id
) AS customer_contact_x

CodePudding user response：

Here is almost the same answer as ggordon, but I used a common table expression and I think the ordering in the subquery portion should go by CONTACT_VS first then by non-NULL email addresses. I created some very simple test data to run this:

DECLARE @CUSTOMER AS TABLE
(
    [ID]           INT         NOT NULL,
    [CUSTOMERNAME] VARCHAR(10) NOT NULL
);

INSERT INTO @CUSTOMER
(
    [ID],
    [CUSTOMERNAME]
)
VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Cathy');

DECLARE @CUSTOMER_CONTACT AS TABLE
(
    [ID]          INT         NOT NULL,
    [CUSTOMER_ID] INT         NOT NULL,
    [CONTACT_VC]  INT         NOT NULL,
    [EMAIL]       VARCHAR(40) NULL
);

INSERT INTO @CUSTOMER_CONTACT
(
    [ID],
    [CUSTOMER_ID],
    [CONTACT_VC],
    [EMAIL]
)
VALUES
(1, 1, 1, '[email protected]'),
(2, 1, 2, '[email protected]'),
(3, 2, 1, NULL),
(4, 2, 2, '[email protected]'),
(5, 3, 1, NULL),
(6, 3, 2, NULL),
(7, 3, 3, NULL);

;WITH [cc]
AS (SELECT [ID],
           [CUSTOMER_ID],
           [CONTACT_VC],
           [EMAIL],
           ROW_NUMBER() OVER (PARTITION BY [CUSTOMER_ID]
                              ORDER BY [CONTACT_VC],
                                       (CASE WHEN [EMAIL] IS NOT NULL THEN
                                                 0
                                             ELSE
                                                 1
                                        END
                                       )
                             ) AS [rn]
    FROM   @CUSTOMER_CONTACT)
SELECT [c].[ID], [c].[CUSTOMERNAME], [cc].[ID], [cc].[CUSTOMER_ID], [cc].[CONTACT_VC], [cc].[EMAIL]
FROM   @CUSTOMER AS [c]
       INNER JOIN [cc]
           ON [c].[ID] = [cc].[CUSTOMER_ID]
              AND [cc].[rn] = 1;

CodePudding user response：

select * from CUSTOMER_CONTACT where EMAIL IS NOT NULL 
union all 
select * from CUSTOMER_CONTACT where 
    (CONTACT_VC=1 and NOT EXISTS (select 1 FROM CUSTOMER_CONTACT where EMAIL IS NOT NUL)
order by CONTACT_VC asc limit 1