SQL join on a one-to-one relationship

Below is sample data from the Entity table, which I need to JOIN to the DiscountRefundInstallmentBound table ON DiscountRefundTableId. The problem is, that sometimes the DiscountRefundInstallmentBound table can have multiple rows for a single DiscountRefundTableId.

select ca.AccountNumber, a.Code, a.DiscountRefundTableId, ca.InstallmentCount, drib.InstallmentUpperBound
from CustomerAccount ca
	join Entity a on a.PartyId = ca.AgentId
	left join DiscountRefundInstallmentBound drib on drib.DiscountRefundTableId = a.DiscountRefundTableId and
 drib.InstallmentUpperBound = "based on a formula for ca.InstallmentCount" --<< need correct SQL code here

Open in new window


The formula to calculate the drib.InstallmentUpperBound (currentBound) in C# is below. How do I convert this into SQL?
        public DiscountRefundInstallmentBound GetInstallmentBound(NInt32 installmentCount)
        {
            DiscountRefundInstallmentBound currentBound = null;
            this.Sort(new GenericListSorter("InstallmentUpperBound.AcceptedValue", false));

            foreach (DiscountRefundInstallmentBound bound in this)
            {
                if (currentBound != null && currentBound.InstallmentUpperBound.Value >= installmentCount) break;
                currentBound = bound;
            }
            if (currentBound == null) return null;
            return currentBound;
        }

Open in new window

select * from DiscountRefundInstallmentBound

Open in new window

DiscountRefundInstallmentBoundId     DiscountRefundTableId     InstallmentUpperBound
1                                                                   1                                             12
2                                                                   1                                             3
LVL 8
pzozulkaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SStoryCommented:
That means it is not 1 to 1. It is 1 to many...period. The one side being DiscountRefundTableId and the many side being DiscountRefundInstallmentB.
SStoryCommented:
Not know what your code does entirely, this might be close:

select * from DiscountRefundTableId r
left join DiscountRefundInstallmentBound b on r.DiscountRefundTableId=b.DiscountRefundTableId
where  drib.InstallmentUpperBound NOT NULL AND drib.InstallmentUpperBound>=InstallmentCount
order by drib.InstallmentUpperBound DESC
pzozulkaAuthor Commented:
The problem with this query is the sample below when let's say the Installment Count is 9, and the DiscountRefundInstallmentBound table looked liked this:

DiscountRefundInstallmentBoundId     DiscountRefundTableId     InstallmentUpperBound
1                                                                   1                                             12
2                                                                   1                                             3
3                                                                   1                                             10

With your suggestion, it would still result in two records being returned for 10 and 12 InstallmentUpperBound.


The C# code simply loops through each InstallmentUpperBound, and checks if >= InstallmentCount. If it is, it BREAKS out of the loop and that's the one it returns, or in this case, should JOIN on.

So with my example here with InstallmentCount = 9, and the table above, it should take the following steps.

1) Order by InstallmentUpperBound
2) Is 3 >= 9
3) Is 10 >= 9
4) JOIN on 10:
join DiscountRefundInstallmentBound drib on drib.DiscountRefundTableId = a.DiscountRefundTableId and drib.InstallmentUpperBound = 10
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulEE Topic AdvisorCommented:
I'm guessing  little. I think you want only the maximum values of [InstallmentUpperBound] from DiscountRefundInstallmentBound. This doesn't require a "formula" or a loop, but I do suggest using an "analytic function" called ROW_NUMBER() and you also need OVER(). Within the OVER clause we can "partition" (which is a little similar to "group by") so that we start numbering rows at 1 when the partition value changes. Then, by using ORDER BY, we control which specific row gets the row number 1 and if we order by [InstallmentUpperBound] DESCending we give the maximum value of that column a row_num of 1. Thus, if we then select where row_num = 1 we are only getting the rows that have the maximums, and we do this as a subquery.
SELECT
      ca.AccountNumber
    , a.Code
    , a.DiscountRefundTableId
    , ca.InstallmentCount
    , drib.InstallmentUpperBound
FROM CustomerAccount ca
JOIN Entity a
      ON a.PartyId = ca.AgentId
LEFT JOIN (
      SELECT
            * --<< you to do
          , ROW_NUMBER() OVER (PARTITION BY DiscountRefundTableId 
                               ORDER BY InstallmentUpperBound DESC) AS row_num
      FROM DiscountRefundInstallmentBound
) drib
      ON drib.DiscountRefundTableId = a.DiscountRefundTableId
      AND drib.row_num = 1
;

Open in new window


{+edit}
NOTE: If you don't want the maximum of  [InstallmentUpperBound] but wanted the highest [DiscountRefundInstallmentBoundId] instead change the ORDER BY within the OVER(...) e.g.
          , ROW_NUMBER() OVER (PARTITION BY DiscountRefundTableId 
                               ORDER BY DiscountRefundInstallmentBoundId DESC) AS row_num

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pzozulkaAuthor Commented:
I think I came up with a solution, that also includes getting the MAX in certain situations when it can't find an InstallmentUpperBound >= ca.InstallmentCount

SELECT
      ca.AccountNumber
    , a.Code
    , a.DiscountRefundTableId
    , ca.InstallmentCount
    , drib.InstallmentUpperBound
from CustomerAccount ca
join Entity a on a.PartyId = ca.AgentId
left join DiscountRefundInstallmentBound drib on drib.DiscountRefundTableId = a.DiscountRefundTableId 
	and drib.InstallmentUpperBound =
		case when 
			(
				select top 1 InstallmentUpperBound 
				from DiscountRefundInstallmentBound
				where DiscountRefundTableId = a.DiscountRefundTableId and InstallmentUpperBound >= ca.InstallmentCount
				order by InstallmentUpperBound
			) 
			is null then -- can be null if InstallmentCount = 24, and the largest InstallmentUpperBound is 12, for example. If that's the case, use the MAX InstallmentUpperBound
			(
				select max(InstallmentUpperBound)
				from DiscountRefundInstallmentBound
				where DiscountRefundTableId = a.DiscountRefundTableId
			)
			else
			(
				select top 1 InstallmentUpperBound 
				from DiscountRefundInstallmentBound
				where DiscountRefundTableId = a.DiscountRefundTableId and InstallmentUpperBound >= ca.InstallmentCount
				order by InstallmentUpperBound
			)
		end

Open in new window

PortletPaulEE Topic AdvisorCommented:
ok. there may be some advantage in using an apply operator so you may reference the subquery outcome by its alias, such as this:
SELECT
      ca.AccountNumber
    , a.Code
    , a.DiscountRefundTableId
    , ca.InstallmentCount
    , drib.InstallmentUpperBound
from CustomerAccount ca
join Entity a on a.PartyId = ca.AgentId
OUTER APPLY (
				select top 1 InstallmentUpperBound 
				from DiscountRefundInstallmentBound
				where DiscountRefundTableId = a.DiscountRefundTableId and InstallmentUpperBound >= ca.InstallmentCount
				order by InstallmentUpperBound
            ) oa (TopInstallmentUpperBound)
left join DiscountRefundInstallmentBound drib on drib.DiscountRefundTableId = a.DiscountRefundTableId 
	and drib.InstallmentUpperBound =
		case when oa.TopInstallmentUpperBound is null then -- can be null if InstallmentCount = 24, and the largest InstallmentUpperBound is 12, for example. If that's the case, use the MAX InstallmentUpperBound
			(
				select max(InstallmentUpperBound)
				from DiscountRefundInstallmentBound
				where DiscountRefundTableId = a.DiscountRefundTableId
			)
			else oa.TopInstallmentUpperBound
		end

Open in new window

SStoryCommented:
OK. Then that sounds like the following except depending if this is SQL Server or MySQL or something else you need a clause to limit it to 1 result per group.  The order by has been changed to ASCENDING to get the min value--which appears to be what you want.  If not, put the DESC keyword back in place. The group by should group them by the id and having the limit statement should give only 1 per group. Of course you could do Max(r.InstallmentUpperBound) or Min(r.InstallmentUpperBound) instead and possibly get it. It depends on what you want.

select  r.InstallmentUpperBound
from DiscountRefundTableId r
left join DiscountRefundInstallmentBound b on r.DiscountRefundTableId=b.DiscountRefundTableId
where  r.InstallmentUpperBound NOT NULL AND r.InstallmentUpperBound>=InstallmentCount
order by r.InstallmentUpperBound
group by r.DiscountRefundTableId
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.