Multiple Joins Showing Wrong # Records

17 Mar 2006 11:09 PM

BenM

I have a query that I need to return 478 rows, which is the number of records
in one of the tables. The problem is that I have to join more than one table
to the query, and I only get the rows that have data, whereas I would prefer
to receive a NULL.

Here is the query:
select z.sponsor_id, min(cov.effective_date), max(cov.termination_date)
from zztmpSPID z
    left outer join coverage cov on z.sponsor_id = cov.sponsor_id
    left outer join policy pol on cov.policy_id = pol.policy_id
where pol.line_id = 10
    and cov.effective_date <= getdate()
    and (cov.termination_date > getdate() OR cov.termination_date is null)
    and (cov.termination_date is null OR cov.effective_date <>
cov.termination_date)
group by z.sponsor_id
order by z.sponsor_id

There are 478 rows in the zzTmpSPID [z] table, and I want every row
returned, whether or not they have data in COVERAGE [cov]. The problem is
that I have to include the POLICY table [pol] to only show certain
information. So, the above query only returns 279 rows, as only 279 of the
records in [z] have a coverage with the associated policy data.

17 Mar 2006 11:22 PM

David Portas

Show quote

"BenM" <REMOVECAPSben@ulgt.orgREMOVECAPS> wrote in message
news:3B6815B3-31E0-4C9A-A6E1-7197FB1F9181@microsoft.com...
>I have a query that I need to return 478 rows, which is the number of
>records
> in one of the tables. The problem is that I have to join more than one
> table
> to the query, and I only get the rows that have data, whereas I would
> prefer
> to receive a NULL.
>
> Here is the query:
> select z.sponsor_id, min(cov.effective_date), max(cov.termination_date)
> from zztmpSPID z
> left outer join coverage cov on z.sponsor_id = cov.sponsor_id
> left outer join policy pol on cov.policy_id = pol.policy_id
> where pol.line_id = 10
> and cov.effective_date <= getdate()
> and (cov.termination_date > getdate() OR cov.termination_date is null)
> and (cov.termination_date is null OR cov.effective_date <>
> cov.termination_date)
> group by z.sponsor_id
> order by z.sponsor_id
>
> There are 478 rows in the zzTmpSPID [z] table, and I want every row
> returned, whether or not they have data in COVERAGE [cov]. The problem is
> that I have to include the POLICY table [pol] to only show certain
> information. So, the above query only returns 279 rows, as only 279 of
> the
> records in [z] have a coverage with the associated policy data.

Move your logic into the ON clauses otherwise the WHERE clause will exclude
the null rows from the outer join. Following is untested:

SELECT z.sponsor_id, MIN(cov.effective_date), MAX(cov.termination_date)
FROM zztmpSPID z
LEFT OUTER JOIN coverage cov
ON z.sponsor_id = cov.sponsor_id
   AND cov.effective_date <= GETDATE()
   AND cov.termination_date > GETDATE()
   AND cov.effective_date <> cov.termination_date
LEFT OUTER JOIN policy pol
ON cov.policy_id = pol.policy_id
   AND pol.line_id = 10
GROUP BY z.sponsor_id
ORDER BY z.sponsor_id ;

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

17 Mar 2006 11:34 PM

BenM

YOU ROCK! Thanks for the fast response on this one, I appreciate it.

Show quote

"David Portas" wrote:

> "BenM" <REMOVECAPSben@ulgt.orgREMOVECAPS> wrote in message
> news:3B6815B3-31E0-4C9A-A6E1-7197FB1F9181@microsoft.com...
> >I have a query that I need to return 478 rows, which is the number of
> >records
> > in one of the tables. The problem is that I have to join more than one
> > table
> > to the query, and I only get the rows that have data, whereas I would
> > prefer
> > to receive a NULL.
> >
> > Here is the query:
> > select z.sponsor_id, min(cov.effective_date), max(cov.termination_date)
> > from zztmpSPID z
> > left outer join coverage cov on z.sponsor_id = cov.sponsor_id
> > left outer join policy pol on cov.policy_id = pol.policy_id
> > where pol.line_id = 10
> > and cov.effective_date <= getdate()
> > and (cov.termination_date > getdate() OR cov.termination_date is null)
> > and (cov.termination_date is null OR cov.effective_date <>
> > cov.termination_date)
> > group by z.sponsor_id
> > order by z.sponsor_id
> >
> > There are 478 rows in the zzTmpSPID [z] table, and I want every row
> > returned, whether or not they have data in COVERAGE [cov]. The problem is
> > that I have to include the POLICY table [pol] to only show certain
> > information. So, the above query only returns 279 rows, as only 279 of
> > the
> > records in [z] have a coverage with the associated policy data.
>
> Move your logic into the ON clauses otherwise the WHERE clause will exclude
> the null rows from the outer join. Following is untested:
>
> SELECT z.sponsor_id, MIN(cov.effective_date), MAX(cov.termination_date)
> FROM zztmpSPID z
> LEFT OUTER JOIN coverage cov
>   ON z.sponsor_id = cov.sponsor_id
>    AND cov.effective_date <= GETDATE()
>    AND cov.termination_date > GETDATE()
>    AND cov.effective_date <> cov.termination_date
> LEFT OUTER JOIN policy pol
>   ON cov.policy_id = pol.policy_id
>    AND pol.line_id = 10
> GROUP BY z.sponsor_id
> ORDER BY z.sponsor_id ;
>
> Hope this helps.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
>