How to selectively join based on if column in right table is null or not?-CodePudding

Imagine you have a Business table.

Id	BusinessName
1	Goodlife
2	BadLife
3	OkayLife

We also have a BusinessContact table containing all contacts for the business.

Id	BusinessId	Email	ContactType
1	1	[email protected]	2
2	1	NULL	1
3	1	[email protected]	3
4	2	[email protected]	1
5	2	NULL	2
6	2	[email protected]	3

I want to join BusinessContact on Business so example sql:

SELECT Business.Id, Email, ContactTypeId FROM Business
JOIN BusinessContact ON Business.Id = BusinessContact.BusinessId
WHERE ContactTypeId IN (1,2) --we only want managers and owners

This would give me the following table:

Id	Email	ContactTypeId
1	[email protected]	2
1	NULL	1
2	[email protected]	1
2	NULL	2

What I want to do is only join the contact which has an email address and also always prioritize owner on the join. So if the owner of the business (ContactTypeId = 1) has no email adress, which is the case for BusinessId = 1. Then join the Manager contact.

So the expected result would be:

Id	Email	ContactTypeId
1	[email protected]	2
2	[email protected]	1

Edge cases:

If both manager and owner email addresses are null then either the business is included with email and contactypeId columns as null or the business is not in the result table. Whichever outcome does not matter.
If both owner and manager have email addresses, we always prioritize owner and that would be the only contact on the resulted table

CodePudding user response：

maybe this can help

select b.id,
       isnull(bc1.email, bc2.email) as email,
       isnull(bc1.contacttypeid, bc2.contacttypeid) as contacttypeid
from   Business b
  outer apply ( select top 1
                       c.email,
                       c.contacttypeid
                from   BusinessContact c
                where  c.businessid = b.id
                and    c.contacttypeid = 1
                and    c.email is not null
              ) bc1
   
  outer apply ( select top 1
                       c.email,
                       c.contacttypeid
                from   BusinessContact c
                where  c.businessid = b.id
                and    c.contacttypeid = 2
              ) bc2

Try it out in this DBFiddle

The result

id	email	contacttypeid
1	[email protected]	2
2	[email protected]	1

CodePudding user response：

We join to the BusinessContacts twice, once to get the owners and once to get the manager and then use coalesce to get the owners Email if we have it and the managers Email if the owners Email is null.

<!-- -->
>     create table Business(
>     id int,
>     bname varchar(20));
>     insert into Business values
>     (1,'Goodlife'),
>     (2,'BadLife');
>     create table BusinessContact(
>     id int,
>     businessId int,
>     Email varchar(30),
>     contactType int);
>     insert into businessContact values
>     (1,   1,  '[email protected]', 2),
>     (2,   1,  NULL,   1),
>     (4,   1,  '[email protected]',    3),
>     (3,   2,  '[email protected]',    1),
>     (4,   2,  NULL,   2),
>     (4,   2,  '[email protected]', 3);
>     GO
> 
> <pre>
8 rows affected
> </pre>

<!-- -->
>     select bname "Business Name",
>     coalesce( o.email, m.email) "Email Adress"
>     from Business b
>     left join 
>       (select businessid, 
>       email from BusinessContact 
>       where contactType = 1) o on b.id=o.businessid
>     left join 
>       (select businessid, 
>       email from BusinessContact 
>       where contactType = 2) m on b.id=m.businessid
>     GO
> 
> <pre>
> Business Name | Email Adress        
> :------------ | :-------------------
> Goodlife      | [email protected]
> BadLife       | [email protected]   
> </pre>

*db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=a53e4481efb5b2190ee474cfaca29989)*

CodePudding user response：

Hey this query should work

SELECT
b.BusinessName, bc.Email, bc.ContactType
FROM Business b
INNER JOIN BusinessContact bc ON(b.Id = bc.BusinessId)

WHERE bc.ContactType = (
SELECT MIN(bc1.ContactType)
FROM BusinessContact bc1
WHERE bc1.BusinessId = b.Id
AND bc1.Email IS NOT NULL
AND bc1.ContactType BETWEEN 1 AND 2)
ORDER BY b.BusinessName

See example here dbfiddle

CodePudding user response：

You want the best contact per business. I suggest to use a lateral join on the top contact row for this:

SELECT b.id, b.businessname, bc.email, bc.contacttypeid
FROM business b
OUTER APPLY
(
  SELECT TOP (1) *
  FROM businesscontact bc
  WHERE bc.businessid = b.id
  AND bc.contacttypeid IN (1, 2)
  ORDER BY
    CASE WHEN bc.email IS NULL THEN 2 ELSE 1 END,
    bc.contacttypeid
)
ORDER BY b.id;

Use CROSS APPLY instead, if you want to dismiss businesses without owner and manager emails. In that case also make the email mandatory:

SELECT b.id, b.businessname, bc.email, bc.contacttypeid
FROM business b
CROSS APPLY
(
  SELECT TOP (1) *
  FROM businesscontact bc
  WHERE bc.businessid = b.id
  AND bc.contacttypeid IN (1, 2)
  AND bc.email IS NOT NULL
  ORDER BY bc.contacttypeid
)
ORDER BY b.id;

CodePudding user response：

SELECT businessid,email,contacttype FROM 
(
SELECT b.id,
c.*,
ROW_NUMBER () OVER (PARTITION BY businessid ORDER BY contacttype) rn 
FROM businesscontact c
INNER JOIN business b
ON b.id = c.businessid
WHERE email IS NOT NULL 
) temp  WHERE temp.rn = 1
ORDER BY businessid;