Use grouped results from one table in condition of a LEFT JOIN on another table

Vote count: 0

This is my first post so please let me know if you need any more details.

I have 3 tables, one of which is a setup table [invrules] and then the other 2 are member data tables [basic] and [invinst]. What I want to do is to display the [invinst].[invcde] and [invinst].[instamt] columns using unique values for that scheme. The [invrules] table has the scheme code in the first 4 characters of the field [schkey]. What I have tried to do is to determine what these are by grouping them together for that scheme and then linking that to each LEFT JOIN I have created to give me the columns. This takes a very long time to run and obviously isn't very efficient.

I have to do this as a SELECT statement rather than using CTE or functions.

This was the attempt that took a long time to deliver the correct result (a couple of minutes):

select getdate() DateRun
    ,b.membno
    ,b.surnam
    ,b.scheme
    ,i1.invcde AS 'Investment Code 1'
    ,i1.instamt AS 'Investment Percentage 1'
    ,i2.invcde AS 'Investment Code 2'
    ,i2.instamt AS 'Investment Percentage 2'
    ,i3.invcde AS 'Investment Code 3'
    ,i3.instamt AS 'Investment Percentage 3'
from basic AS b
left join
(
    select i.invcde, RANK() OVER (ORDER BY i.invcde) AS [RANK]
    from invrules AS i
    where left(schkey, 4) = 'ABCD'
    and (i.enddte is null or i.enddte > getdate())
    group by i.invcde
) AS [invcde_list_1] ON [invcde_list_1].Rank = 1
left join
(
    select i.invcde, RANK() OVER (ORDER BY i.invcde) AS [RANK]
    from invrules AS i
    where left(schkey, 4) = 'ABCD'
    and (i.enddte is null or i.enddte > getdate())
    group by i.invcde
) AS [invcde_list_2] ON [invcde_list_2].Rank = 2
left join
(
    select i.invcde, RANK() OVER (ORDER BY i.invcde) AS [RANK]
    from invrules AS i
    where left(schkey, 4) = 'ABCD'
    and (i.enddte is null or i.enddte > getdate())
    group by i.invcde
) AS [invcde_list_3] ON [invcde_list_3].Rank = 3
left join invinst AS i1 on b.membno = i1.membno and i1.invcde = [invcde_list_1].invcde and i1.contsrc = 'EE' and i1.enddte is null and [invcde_list_1].Rank = 1
left join invinst AS i2 on b.membno = i2.membno and i2.invcde = [invcde_list_2].invcde and i2.contsrc = 'EE' and i2.enddte is null and [invcde_list_2].Rank = 2
left join invinst AS i3 on b.membno = i3.membno and i3.invcde = [invcde_list_3].invcde and i3.contsrc = 'EE' and i3.enddte is null and [invcde_list_3].Rank = 3
where b.membno >= 15000
and b.scheme = 'ABCD'
order by b.membno

I tried to use a CROSS JOIN instead as the [invinst] table doesn't need to join to anything. This returned data from the [basic] table on each line and the matching data from the [invinst] table however there were additional rows because of the CROSS JOIN which gave Null values in the columns derived from the [invinst] table. This was the code:

select getdate() DateRun
    ,b.membno
    ,b.surnam
    ,b.scheme
    ,i1.invcde AS 'Investment Code 1'
    ,i1.instamt AS 'Investment Percentage 1'
    ,i2.invcde AS 'Investment Code 2'
    ,i2.instamt AS 'Investment Percentage 2'
    ,i3.invcde AS 'Investment Code 3'
    ,i3.instamt AS 'Investment Percentage 3'
from basic AS b
cross join
(
    select i.invcde, RANK() OVER (ORDER BY i.invcde) AS [RANK]
    from invrules AS i
    where left(dcschkey, 4) = 'ABCD'
    and (i.enddte is null or i.enddte > getdate())
    group by i.invcde
) AS [invcde_list]
left join invinst AS i1 on b.membno = i1.membno and i1.invcde = [invcde_list].invcde and i1.contsrc = 'EE' and i1.enddte is null and [invcde_list].Rank = 1
left join invinst AS i2 on b.membno = i2.membno and i2.invcde = [invcde_list].invcde and i2.contsrc = 'EE' and i2.enddte is null and [invcde_list].Rank = 2
left join invinst AS i3 on b.membno = i3.membno and i3.invcde = [invcde_list].invcde and i3.contsrc = 'EE' and i3.enddte is null and [invcde_list].Rank = 3
where b.membno >= 15000
and b.scheme = 'ABCD'
order by b.membno

This is some test data:

[basic]
membno  surnam  scheme
14000   Jones   ABCD
15000   Smith   ABCD
15001   Henry   ABCD
15002   Mabel   ABCD
15003   McDonald    ABCD

[invinst]
membno  contsrc invcde  instamt
14000   EE  CD01    100
15000   EE  CD01    50
15000   EE  CD02    50
15000   ER  CD01    50
15001   EE  CD02    100
15002   EE  CD01    100
15003   EE  CD01    100
15003   ER  CD01    100

[invrules]
schkey  contsrc enddte  invcde
ABCDCAT1    EE      CD01
ABCDCAT1    ER      CD01
ABCDCAT1    EE      CD02
ABCDCAT1    ER      CD02
ABCDCAT1    EE      CD03
ABCDCAT1    ER      CD03
ABCDCAT2    EE      CD01
ABCDCAT2    ER      CD01
ABCDCAT2    EE      CD02
ABCDCAT2    ER      CD02
ABCDCAT2    EE      CD03
ABCDCAT2    ER      CD03
ABCDCAT3    EE      CD01
ABCDCAT3    EE      CD02
ABCDCAT3    EE      CD03
ABCDCAT4    EE      CD01
ABCDCAT4    EE      CD02
ABCDCAT4    EE      CD03
ABBBCAT1    EE      CD01
ABBBCAT1    EE      CD02
ABBBCAT2    EE      CD01
ABBBCAT3    EE      CD01

I have had to modify the data somewhat to anonymise it but hopefully you will understand what I am trying to achieve.

Without using lots of sub queries is there a way I can summarise the [invrules] table for the scheme and use that within the LEFT JOINs that is more efficient? Or is there a different approach?

Thanks,

Darrell

asked 28 secs ago

Darrell H

Let's block ads! (Why?)

Use grouped results from one table in condition of a LEFT JOIN on another table