Re: OUTER JOIN Problem
From: Jeff Shantz (jeffshantz_at_msn.com)
Date: 06/28/04
- Next message: Anith Sen: "Re: OUTER JOIN Problem"
- Previous message: Helene Day: "How to return the key to the added record using store procedure."
- In reply to: Scott Morris: "Re: OUTER JOIN Problem"
- Next in thread: Anith Sen: "Re: OUTER JOIN Problem"
- Reply: Anith Sen: "Re: OUTER JOIN Problem"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 28 Jun 2004 12:56:51 -0700
Scott,
I'm not hitting on you, but you are a beautiful, beautiful
man. When I have children, they will all be named Scott.
I've been hitting my head on the desk over this problem
for hours now, and it was driving me crazy.
The UNION ALL query worked just as you suggested, and I
thank you very much for your help!
Regards,
Jeff Shantz
>-----Original Message-----
>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 >> > > >. >
- Next message: Anith Sen: "Re: OUTER JOIN Problem"
- Previous message: Helene Day: "How to return the key to the added record using store procedure."
- In reply to: Scott Morris: "Re: OUTER JOIN Problem"
- Next in thread: Anith Sen: "Re: OUTER JOIN Problem"
- Reply: Anith Sen: "Re: OUTER JOIN Problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
