INNER JOIN MULTIPLE TABLES 1

[cristi22]

Hello All -

Thanks in advance for anyone who takes time to help!

I have to combined the following 2 statements

1.

SELECT distinct  l.ssn,l.period, r.period FROM umass_contr l inner join  umass_contr r on l.period = (date('2005-07-01') - 6 month)  and r.period =(date('2005-07-01') - 1 month) and l.ssn = r.ssn

2.

SELECT distinct  e.employer , e.period,
    (case e.app when  'F' then (case e.MARIT when 'M'  then 'STANDARD_FAMILY' else 'STANDARD_SINGLE' end) else '' end) plandesc,
      e.ssn, e.app, e.marit
    FROM umass_contr e
        WHERE
    employer='9990001' 
and e.ssn='019249046' and
    e.period = date('2005-07-01')
    and e.APP IN ('F' )
    and e.ssn not in ( select  distinct ssn from umass_contr  where app in ('SFHS','SFHF')  and period = date('2005-07-01')   )
--    and l.ssn not in ( select  distinct ssn from umass_contr  where nk_type='NK'  and period = date('2005-07-01')   )

The first select creates a table with members who has 6 consecutive months (period) of contributions

The second select creates a table with members who has contributions in 07/01/2005 & all the rest of criteria in the statement

The final table should have only matching records from the above

For example, if 8888888 ssn have contributions in 07/01/2005, but does not have 6 consequtive month of contributions should not be on the final table


THANKS A LOT!

 

[ddiamond]

cristi22,

Does your first statement actually return members with 6 consecutive months of contributions? Wouldn't you need something like:

SELECT distinct l.ssn
FROM umass_contr l
WHERE
  6 = (
         select count(*) from umass_contr 
         where ssn = l.snn
         and period between (date('2005-07-01') - 6 month) 
         and (date('2005-07-01') - 1 month)
      )

I can help you combine your statements above, but I first want to make sure your statements do what you intend them to do.

 

[cristi22]

Thanks a lot ddiamond!!!
Really appreciate your help & your time!!!

Yes, it does

The user enters the date (2005-07-01, I hard coded for test)

Firstly, check if this member has 6 consecutive months of contributions, and then select all the data with '2005-07-01' and the rest of the criteria (bypass certain app & nk types)

Your statement is not completed right?
I did not excecute.

The statement is a part of SP

THANKS AGAIN,
Cristi

 

[ddiamond]

The following query will only return records that match both the criteria from statement 1 and 2.

SELECT distinct  e.employer , e.period,
    (case e.app when  'F' then (case e.MARIT when 'M'  then 'STANDARD_FAMILY' else 'STANDARD_SINGLE' end) else '' end) plandesc,
      e.ssn, e.app, e.marit
[blue]FROM umass_contr e inner join umass_contr r on 
  e.period = (date('2005-07-01') - 6 month)  and 
  r.period =(date('2005-07-01') - 1 month) and 
  e.ssn = r.ssn[/blue] 
WHERE
    employer='9990001' 
and e.ssn='019249046' and
    e.period = date('2005-07-01')
    and e.APP IN ('F' )
    and e.ssn not in ( select  distinct ssn from umass_contr  where app in ('SFHS','SFHF')  and period = date('2005-07-01')   )
--    and l.ssn not in ( select  distinct ssn from umass_contr  where nk_type='NK'  and period = date('2005-07-01')   )

 

[cristi22]

Thank you very much for your help!

The above statement did not return any records?
Here is my code, I did not verify the data yet & it takes a while to run

SELECT distinct  e.employer , e.period,
    (case e.app when  'F' then (case e.MARIT when 'M'  then 'STANDARD_FAMILY' else 'STANDARD_SINGLE' end) else '' end) plandesc,
      e.ssn, e.app, e.marit
FROM umass_contr e inner join umass_contr r on 
  e.period = (date('2005-07-01') - 6 month)  and 
  r.period =(date('2005-07-01') - 1 month) and 
  e.ssn = r.ssn 
WHERE
    employer='9990001' 
and e.ssn='019249046' and
    e.period = date('2005-07-01')
    and e.APP IN ('F' )
    and e.ssn not in ( select  distinct ssn from umass_contr  where app in ('SFHS','SFHF')  and period = date('2005-07-01')   )
--    and l.ssn not in ( select  distinct ssn from umass_contr  where nk_type='NK'  and period = date('2005-07-01')   )

 

[ddiamond]

Does both statement 1 and statement 2 return results for ssn=019249046, employer=9990001?

 

[cristi22]

Thank you for your help!

I am using ssn & employer just for the test purposes
Actually, I also need to group the data.
Here is what I came up with:
Not sure if it's 100% correct

SELECT distinct  e.employer , e.period, count( distinct e.ssn), (case e.app when  'F' then (case e.MARIT when 'M'  then 'STANDARD_FAMILY' else 'STANDARD_SINGLE' end) else '' end) plandesc
FROM umass_contr l inner join  umass_contr r on l.period = (date('2005-07-01') - 6 month)  and r.period =(date('2005-07-01') - 1 month) and l.ssn = r.ssn  
inner join umass_contr e on e.ssn=l.ssn and e.ssn=r.ssn and l.employer=r.employer and e.employer=r.employer 
        WHERE
    and e.period = date('2005-07-01') 
    and e.APP IN ('F' )
    and e.ssn not in ( select  distinct ssn from umass_contr  where app in ('SFHS','SFHF')  and period = date('2005-07-01')   )
    and e.ssn not in ( select  distinct ssn from umass_contr  where nk_type='NK'  and period = date('2005-07-01')   )          
  GROUP BY
    e.employer,  e.period,
    (case e.app when   'F' then (case e.MARIT when 'M'  then 'STANDARD_FAMILY' else 'STANDARD_SINGLE' end) else ''end)

thanks a million!!!
cristi

 

[ddiamond]

I think your query will work. I have listed your 2nd inner join below. You can remove the lines highlighted in red as they are redundant, but they should not hurt anything if you leave them in. Just a warning, give it time to finish running. I predict it will be very slow.

  inner join umass_contr e on 
    e.ssn=l.ssn and 
    [red]e.ssn=r.ssn and[/red] 
    l.employer=r.employer and 
    [red]e.employer=r.employer[/red]

 

[ddiamond]

oops, I marked the wrong lines in red. My correction is below.

  inner join umass_contr e on 
    [red]e.ssn=l.ssn and[/red] 
    e.ssn=r.ssn and 
    [red]l.employer=r.employer and[/red] 
    e.employer=r.employer

You also may need to add the condition l.employer = r.employer to your first inner join

FROM umass_contr l 
  inner join  umass_contr r on 
    l.period = (date('2005-07-01') - 6 month)  
    and r.period =(date('2005-07-01') - 1 month)
    and l.ssn = r.ssn  
    [blue]and l.employer = r.employer[/blue]

 

[cristi22]

ddiamond -
thank you very much for your help!

 

[cristi22]

It's creating a temp table l & r & than I'd have to join the result set to the e table, so if I eliminate
e.ssn=l.ssn and
e.ssn=r.ssn and
l.employer=r.employer and
the result would be the same?

thanks,
cristi

 

[ddiamond]

You don't want to eliminate l.employer = r.employer. You want to move it to the the first inner join.

Then after you join l to r,
both l.ssn=r.snn, and l.employer=r.employer, correct?

so you have 2 choices for your 2nd inner join with e:
1.
inner join e on
e.ssn = l.ssn and
e.employer = l.employer

2.
inner join e on
e.ssn = r.ssn and
e.employer = r.employer

It doesn't matter which one you pick because both l and r should have the same employer and same ssn at this point.

Let me know if I am making any sense.

 

[cristi22]

thank you very much for your help!
So, I dont' have to join e on both l & r?

 

[ddiamond]

Correct. You only have to join to one of them. In this case, it doesn't matter which one. The result should be the same.