sub select causing spool issue

[craiogram]

Without the sub select I can use this query to get my result of group op ID with a Max eff date:
[COLOR=green yellow]
select b.group_id, MAX(a.eff_dte)
from
iw_table1 a
,iw_table2 b

where
a.client_id = b.group_client_id
and b.carrier_id = ('2368')

Group by 1
[/color]

However I need to see this product code but maintain the max eff date per group. There are multiple product codes for each group with different dates. I tried using this query but it's given me spool issues. Can you please help tweak?

[COLOR=green yellow]
select b.group_id, a.retro_product_type_cde, a.eff_dte

from
iw_table1 a
,iw_table2 b,

(select d.group_id, MAX(c.eff_dte)
from
iw_table1 c,iw_table2 d
group by 1)maxresults

where
a.client_id = b.group_client_id
and b.carrier_id = ('2368')
and a.eff_dte = maxresults.Maxdate

Group by 1,2,3
[/color]

 

[rac2]

The code you posted has two problems in the subquery, one of which would give a syntax error, I should think.

...
(select d.group_id, MAX(c.eff_dte) AS [COLOR=red]Maxdate[/color]
    from
    iw_table1 c,iw_table2 d
    [COLOR=red]WHERE c.client_id = d.group_client_id [/color]
    group by 1)maxresults
...

You need to JOIN the tables in the subquery as well. As it is written you have a cross product join, every row in table1 is matched with every row in table2.

 

[craiogram]

I'm still getting a spool space issue. Am I not optimizing my joins well?

[COLOR=green yellow]
select b.group_id, a.retro_product_type_cde, a.eff_dte

from
iw_table1 a
,iw_table2 b,

(select d.group_id, MAX(c.eff_dte) as Maxdate
from
iw_table1 c
,iw_table2 d
where
c.client_id = d.group_client_id
group by 1)maxresults

where
a.client_id = b.group_client_id
and b.carrier_id = ('2368')
and a.eff_dte = maxresults.Maxdate

Group by 1,2,3
[/color]

 

[PHV]

As you asked in the JetSQL forum, here a suggestion with ms-access syntax:
SELECT B.group_id, A.retro_product_type_cde, A.eff_dte
FROM (iw_table1 A
INNER JOIN iw_table2 B ON A.client_id = B.group_client_id)
INNER JOIN (
SELECT D.group_id, MAX(C.eff_dte) AS Maxdate
FROM iw_table1 C INNER JOIN iw_table2 D ON C.client_id = D.group_client_id
WHERE D.carrier_id = '2368' GROUP BY 1
) E ON A.eff_dte = E.Maxdate
WHERE B.group_id = E.group_id AND B.carrier_id = '2368'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

Read the JOIN article linked below for more on joins. You're using a cartesian product (FROM Table1, Table2) which multiples the tables rather than joining on specific information.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins