Re: OUTER JOIN Problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Scott Morris (bogus_at_bogus.com)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 15:16:50 -0400

Don't outer join. Use a union. Below is pseuodo code for you to work
through.

select ...
from (
select user, date, sum(incalls) as incalls, 0 as saves, 0 as tickets
from calls where...
aggregating by user / date
union all
select user, date, 0, sum(saves), sum(tickets)
from saves where...
aggregating by user / date
) as aggdata
where ...
order by ...

Ultimately, the problem lies with your application of the where criteria and
your assumptions about the order in which the query is processed. You only
want calls and saves from a specific time period. Unfortunately, the where
criteria are applied after the join is processed. So even though you did an
outer join, the where clause effectively converted it to an inner join since
it did not correctly account for the NULL values generated by the outer
join. Note - read BOL carefully for the syntax - you will want to use union
ALL!

"Jeff Shantz" <jeffshantz@msn.com> wrote in message
news:2278401c45d34$109ffee0$a401280a@phx.gbl...
> Hello all,
>
> I'm having a problem with a query I'm working on.
>
> I have two tables as shown below (only the relevant fields
> are displayed):
>
> ----------------------------------------------------------
>
> tbl_Stats_Calls
> -------------------------
> callDate - smalldatetime
> userID - bigint
> in_calls - smallint
>
>
> tbl_Stats_Saves
> -------------------------
> saveDate - smalldatetime
> userID - bigint
> saves - smallint
> tickets - smallint
> ----------------------------------------------------------
>
> Basically, I want to join these two tables together so
> that it displays a record for each user in either the
> calls or saves table, and their respective saves, tickets,
> and calls for that particular day. However, there may be
> some days when a user would have no entries in the calls
> table, or no entries in the saves table. So, I want the
> output to be as follows:
>
> dtDate userID calls saves tickets
> ------------------------------------------------
> 02/10/2004 12 50 23 50
> 02/15/2004 16 12 <null> <null>
> 02/18/2004 13 <null> 35 53
>
> I have then further modified the query so that instead of
> displaying nulls, it displays 0's, as follows:
>
> dtDate userID calls saves tickets
> ------------------------------------------------
> 02/10/2004 12 50 23 50
> 02/15/2004 16 12 0 0
> 02/18/2004 13 0 35 53
>
> My problem is that, depending on how I join the tables, I
> either get the correct amount of saves/tickets, or I get
> the correct amount of calls, but
> not both. Here's my query:
>
> SELECT
>
> ISNULL(dbo.tbl_Stats_Saves.saveDate,
> dbo.tbl_Stats_Calls.callDate) as
> dtDate,
> ISNULL(dbo.tbl_Stats_Saves.userID,
> dbo.tbl_Stats_Calls.userID) AS userID,
> ISNULL(dbo.tbl_Stats_Calls.in_calls, 0) as in_calls,
> ISNULL(dbo.tbl_Stats_Saves.saves, 0) AS saves,
> ISNULL(dbo.tbl_Stats_Saves.noSaves, 0) AS noSaves,
> ISNULL(dbo.tbl_Stats_Saves.noAttempts, 0) AS noAttempts,
> ISNULL(dbo.tbl_Stats_Saves.tickets, 0) AS tickets
>
> FROM
>
> dbo.tbl_Stats_Saves FULL OUTER JOIN dbo.tbl_Stats_Calls ON
> dbo.tbl_Stats_Saves.userID = dbo.tbl_Stats_Calls.userID AND
> dbo.tbl_Stats_Saves.saveDate = dbo.tbl_Stats_Calls.callDate
>
> WHERE (dbo.tbl_Stats_Saves.saveDate BETWEEN @start AND
> @end))
>
> I have also tried adding the following to the WHERE
> condition, but then all
> my numbers get completely messed: "AND
> (dbo.tbl_Stats_Calls.callDate
> BETWEEN @start AND @end)"
>
> If anyone has any suggestions or ideas that may assist, I
> would be most appreciative. Thank you for your time.
>
> Regards,
> Jeff
>

Relevant Pages

  • Entire script
    ... Here is the entire script. ... --rtrimas VendorItem, ... LEFT OUTER JOIN IV00101 AS D ON D.ITEMNMBR = B.ITEMNMBR ... "all queries in an SQL statement containing a UNION operator must have ...
    (microsoft.public.sqlserver.programming)
  • Re: How to join 2 Access tables to return ALL records from both tables
    ... Since you have already eliminated the duplicates ... change to UNION ALL for better performance. ... > Do you mean you want a FULL OUTER JOIN? ... > You recognize the right join as finding records in x not in y, ...
    (microsoft.public.access.queries)
  • Re: Shouldnt a LEFT JOIN work this way?
    ... You might be able to use a UNION query to get a vertical list of parts required and parts shipped. ... SELECT PartNo, Quantity, "Required" as Status ... If you want to directly emulate a full outer join then you still need to use a Union query. ...
    (microsoft.public.access.queries)
  • Re: OUTER JOIN Problem
    ... Sounds like you should use FULL OUTER JOIN. ... > dbo.tbl_Stats_Saves.userID) AS userID, ... > COALESCE(dbo.tbl_Stats_Saves.tickets,0) AS tickets, ... >>I'm having a problem with a query I'm working on. ...
    (microsoft.public.sqlserver.programming)
  • Re: Full outer join and Union again !!
    ... Choose the columns in the two queries so that the column order is the same. ... Union the two queries: ... are project_id and transaction_id and I have anoher table called budget ... > I have tried the left outer join assuming that my transaction table is the ...
    (microsoft.public.access.queries)