OUTER JOIN Problem  
Author Message
carollgarden_girl





PostPosted: Mon Jun 28 12:30:37 CDT 2004 Top

SQL Server Developer >> OUTER JOIN Problem

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


@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


If anyone has any suggestions or ideas that may assist, I
would be most appreciative. Thank you for your time.

Regards,
Jeff

SQL Server252  
 
 
Jeff





PostPosted: Mon Jun 28 12:30:37 CDT 2004 Top

SQL Server Developer >> OUTER JOIN Problem I have also tried the following query, with the exact same
results:

SELECT

COALESCE(dbo.tbl_Stats_Calls.callDate,
dbo.tbl_Stats_Saves.saveDate) as dtDate,

COALESCE(dbo.tbl_Stats_calls.userID,
dbo.tbl_Stats_Saves.userID) AS userID,

COALESCE(dbo.tbl_Stats_Calls.in_calls,0) as in_calls,
COALESCE(dbo.tbl_Stats_Saves.saves,0) AS saves,
COALESCE(dbo.tbl_Stats_Saves.noSaves,0) AS noSaves,
COALESCE(dbo.tbl_Stats_Saves.noAttempts,0) AS noAttempts,
COALESCE(dbo.tbl_Stats_Saves.tickets,0) AS tickets,

ABS(COALESCE(dbo.tbl_Stats_Saves.tickets,0) - COALESCE
(dbo.tbl_Stats_Calls.in_calls,0)) as tcv

FROM

dbo.tbl_Stats_Calls FULL OUTER JOIN dbo.tbl_Stats_Saves
ON
dbo.tbl_Stats_Calls.userID = dbo.tbl_Stats_Saves.userID AND
dbo.tbl_Stats_Saves.saveDate = dbo.tbl_Stats_Calls.callDate










>-----Original Message-----
>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
>


>
>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

>
>If anyone has any suggestions or ideas that may assist, I
>would be most appreciative. Thank you for your time.
>
>Regards,
>Jeff
>
>.
>
 
 
Laurent





PostPosted: Mon Jun 28 13:36:47 CDT 2004 Top

SQL Server Developer >> OUTER JOIN Problem Sounds like you should use FULL OUTER JOIN.

HIH,
--
LR


> I have also tried the following query, with the exact same
> results:
>
> SELECT
>
> COALESCE(dbo.tbl_Stats_Calls.callDate,
> dbo.tbl_Stats_Saves.saveDate) as dtDate,
>
> COALESCE(dbo.tbl_Stats_calls.userID,
> dbo.tbl_Stats_Saves.userID) AS userID,
>
> COALESCE(dbo.tbl_Stats_Calls.in_calls,0) as in_calls,
> COALESCE(dbo.tbl_Stats_Saves.saves,0) AS saves,
> COALESCE(dbo.tbl_Stats_Saves.noSaves,0) AS noSaves,
> COALESCE(dbo.tbl_Stats_Saves.noAttempts,0) AS noAttempts,
> COALESCE(dbo.tbl_Stats_Saves.tickets,0) AS tickets,
>
> ABS(COALESCE(dbo.tbl_Stats_Saves.tickets,0) - COALESCE
> (dbo.tbl_Stats_Calls.in_calls,0)) as tcv
>
> FROM
>
> dbo.tbl_Stats_Calls FULL OUTER JOIN dbo.tbl_Stats_Saves
> ON
> dbo.tbl_Stats_Calls.userID = dbo.tbl_Stats_Saves.userID AND
> dbo.tbl_Stats_Saves.saveDate = dbo.tbl_Stats_Calls.callDate
>
>

>
>
>
>
>
>
>
> >-----Original Message-----
> >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
> >


> >
> >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

> >
> >If anyone has any suggestions or ideas that may assist, I
> >would be most appreciative. Thank you for your time.
> >
> >Regards,
> >Jeff
> >
> >.
> >


 
 
Jeff





PostPosted: Mon Jun 28 13:42:32 CDT 2004 Top

SQL Server Developer >> OUTER JOIN Problem Thanks, but if you'll notice in my query, I did use a full=20
outer join.

>-----Original Message-----
>Sounds like you should use FULL OUTER JOIN.
>
>HIH,
>--
>LR
>


>> I have also tried the following query, with the exact=20
same
>> results:
>>
>> SELECT
>>
>> COALESCE(dbo.tbl_Stats_Calls.callDate,
>> dbo.tbl_Stats_Saves.saveDate) as dtDate,
>>
>> COALESCE(dbo.tbl_Stats_calls.userID,
>> dbo.tbl_Stats_Saves.userID) AS userID,
>>
>> COALESCE(dbo.tbl_Stats_Calls.in_calls,0) as in_calls,
>> COALESCE(dbo.tbl_Stats_Saves.saves,0) AS saves,
>> COALESCE(dbo.tbl_Stats_Saves.noSaves,0) AS noSaves,
>> COALESCE(dbo.tbl_Stats_Saves.noAttempts,0) AS=20
noAttempts,
>> COALESCE(dbo.tbl_Stats_Saves.tickets,0) AS tickets,
>>
>> ABS(COALESCE(dbo.tbl_Stats_Saves.tickets,0) - COALESCE
>> (dbo.tbl_Stats_Calls.in_calls,0)) as tcv
>>
>> FROM
>>
>> dbo.tbl_Stats_Calls FULL OUTER JOIN dbo.tbl_Stats_Saves
>> ON
>> dbo.tbl_Stats_Calls.userID =3D dbo.tbl_Stats_Saves.userID=20
AND
>> dbo.tbl_Stats_Saves.saveDate =3D=20
dbo.tbl_Stats_Calls.callDate
>>
>>

@end)
>>
>>
>>
>>
>>
>>
>>
>> >-----Original Message-----
>> >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=20
be
>> >some days when a user would have no entries in the=20
calls
>> >table, or no entries in the saves table. So, I want=20
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=20
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=20
tables, I
>> >either get the correct amount of saves/tickets, or I=20
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=20
noAttempts,
>> >ISNULL(dbo.tbl_Stats_Saves.tickets, 0) AS tickets
>> >
>> >FROM
>> >
>> >dbo.tbl_Stats_Saves FULL OUTER JOIN=20
dbo.tbl_Stats_Calls ON
>> >dbo.tbl_Stats_Saves.userID =3D dbo.tbl_Stats_Calls.userID
>> AND
>> >dbo.tbl_Stats_Saves.saveDate =3D
>> dbo.tbl_Stats_Calls.callDate
>> >


>> >
>> >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

>> >
>> >If anyone has any suggestions or ideas that may=20
assist, I
>> >would be most appreciative. Thank you for your time.
>> >
>> >Regards,
>> >Jeff
>> >
>> >.
>> >
>
>
>.
>
 
 
Laurent





PostPosted: Mon Jun 28 14:03:01 CDT 2004 Top

SQL Server Developer >> OUTER JOIN Problem Did you try to run your query after suppressing the WHERE clause (which presumably excludes all which don't exist in
tbl_Stats_Calls.

BTW: it would be very helpful if you coul post DDL (create table + insert into + select)


Thanks, but if you'll notice in my query, I did use a full
outer join.

>-----Original Message-----
>Sounds like you should use FULL OUTER JOIN.
>
>HIH,
>--
>LR
>


>> I have also tried the following query, with the exact
same
>> results:
>>
>> SELECT
>>
>> COALESCE(dbo.tbl_Stats_Calls.callDate,
>> dbo.tbl_Stats_Saves.saveDate) as dtDate,
>>
>> COALESCE(dbo.tbl_Stats_calls.userID,
>> dbo.tbl_Stats_Saves.userID) AS userID,
>>
>> COALESCE(dbo.tbl_Stats_Calls.in_calls,0) as in_calls,
>> COALESCE(dbo.tbl_Stats_Saves.saves,0) AS saves,
>> COALESCE(dbo.tbl_Stats_Saves.noSaves,0) AS noSaves,
>> COALESCE(dbo.tbl_Stats_Saves.noAttempts,0) AS
noAttempts,
>> COALESCE(dbo.tbl_Stats_Saves.tickets,0) AS tickets,
>>
>> ABS(COALESCE(dbo.tbl_Stats_Saves.tickets,0) - COALESCE
>> (dbo.tbl_Stats_Calls.in_calls,0)) as tcv
>>
>> FROM
>>
>> dbo.tbl_Stats_Calls FULL OUTER JOIN dbo.tbl_Stats_Saves
>> ON
>> dbo.tbl_Stats_Calls.userID = dbo.tbl_Stats_Saves.userID
AND
>> dbo.tbl_Stats_Saves.saveDate =
dbo.tbl_Stats_Calls.callDate
>>
>>

@end)
>>
>>
>>
>>
>>
>>
>>
>> >-----Original Message-----
>> >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
>> >


>> >
>> >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

>> >
>> >If anyone has any suggestions or ideas that may
assist, I
>> >would be most appreciative. Thank you for your time.
>> >
>> >Regards,
>> >Jeff
>> >
>> >.
>> >
>
>
>.
>


 
 
Jeff





PostPosted: Mon Jun 28 14:15:03 CDT 2004 Top

SQL Server Developer >> OUTER JOIN Problem Hi,

I tried removing the WHERE clause, and the totals that my=20
query reports are as follows:

Saves: 304420
Calls: 675964

If I do two direct SUM() queries on the tbl_Stats_Calls=20
and tbl_Stats_Saves tables respectively, I get the=20
following correct totals:

Saves: 304420
Calls: 675936

So, as you can see, the saves in my query are reported=20
correctly, but the call total that my query reports is 28=20
higher than the actual total number of calls in=20
tbl_Stats_Calls. It's boggling me to no end.

Here are my DDL's. If you need anything else, please let=20
me know. Thanks for your assistance.



CREATE TABLE [tbl_Stats_Calls] (
[callID] [bigint] IDENTITY (1, 1) NOT NULL ,
[userID] [bigint] NOT NULL ,
[supID] [bigint] NOT NULL ,
[callDate] [smalldatetime] NOT NULL ,
[ext] [smallint] NOT NULL ,
[time_login] [int] NOT NULL ,
[time_idle] [int] NOT NULL ,
[time_wait] [int] NOT NULL ,
[time_wrap] [int] NOT NULL ,
[time_hold] [int] NOT NULL ,
[in_calls] [int] NOT NULL ,
[in_time_wrap] [int] NOT NULL ,
[in_time_talk] [int] NOT NULL ,
[out_calls] [int] NOT NULL ,
[out_calls_incomplete] [int] NOT NULL ,
[out_time_wrap] [int] NOT NULL ,
[out_time_talk] [int] NOT NULL ,
CONSTRAINT [PK_tbl_Stats_Calls] PRIMARY KEY =20
CLUSTERED=20
(
[callID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tbl_Stats_Calls_tbl_Users_Agents]=20
FOREIGN KEY=20
(
[userID]
) REFERENCES [tbl_Users_Agents] (
[userID]
),
CONSTRAINT [FK_tbl_Stats_Calls_tbl_Users_Agents1]=20
FOREIGN KEY=20
(
[supID]
) REFERENCES [tbl_Users_Agents] (
[userID]
)
) ON [PRIMARY]



CREATE TABLE [tbl_Stats_Saves] (
[saveID] [bigint] IDENTITY (1, 1) NOT NULL ,
[userID] [bigint] NOT NULL ,
[supID] [bigint] NOT NULL ,
[saveDate] [smalldatetime] NOT NULL ,
[saves] [smallint] NOT NULL ,
[noSaves] [smallint] NOT NULL ,
[noAttempts] [smallint] NOT NULL ,
[tickets] [smallint] NOT NULL ,
CONSTRAINT [PK_Saves] PRIMARY KEY CLUSTERED=20
(
[saveID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tbl_Stats_Saves_tbl_Users_Agents]=20
FOREIGN KEY=20
(
[userID]
) REFERENCES [tbl_Users_Agents] (
[userID]
),
CONSTRAINT [FK_tbl_Stats_Saves_tbl_Users_Agents1]=20
FOREIGN KEY=20
(
[supID]
) REFERENCES [tbl_Users_Agents] (
[userID]
)
) ON [PRIMARY]


Regards,
Jeff Shantz


>-----Original Message-----
>Did you try to run your query after suppressing the WHERE=20
clause (which presumably excludes all which don't exist in
>tbl_Stats_Calls.
>
>BTW: it would be very helpful if you coul post DDL=20
(create table + insert into + select)
>


>Thanks, but if you'll notice in my query, I did use a full
>outer join.
>
>>-----Original Message-----
>>Sounds like you should use FULL OUTER JOIN.
>>
>>HIH,
>>--
>>LR
>>


>>> I have also tried the following query, with the exact
>same
>>> results:
>>>
>>> SELECT
>>>
>>> COALESCE(dbo.tbl_Stats_Calls.callDate,
>>> dbo.tbl_Stats_Saves.saveDate) as dtDate,
>>>
>>> COALESCE(dbo.tbl_Stats_calls.userID,
>>> dbo.tbl_Stats_Saves.userID) AS userID,
>>>
>>> COALESCE(dbo.tbl_Stats_Calls.in_calls,0) as in_calls,
>>> COALESCE(dbo.tbl_Stats_Saves.saves,0) AS saves,
>>> COALESCE(dbo.tbl_Stats_Saves.noSaves,0) AS noSaves,
>>> COALESCE(dbo.tbl_Stats_Saves.noAttempts,0) AS
>noAttempts,
>>> COALESCE(dbo.tbl_Stats_Saves.tickets,0) AS tickets,
>>>
>>> ABS(COALESCE(dbo.tbl_Stats_Saves.tickets,0) - COALESCE
>>> (dbo.tbl_Stats_Calls.in_calls,0)) as tcv
>>>
>>> FROM
>>>
>>> dbo.tbl_Stats_Calls FULL OUTER JOIN dbo.tbl_Stats_Saves
>>> ON
>>> dbo.tbl_Stats_Calls.userID =3D dbo.tbl_Stats_Saves.userID
>AND
>>> dbo.tbl_Stats_Saves.saveDate =3D
>dbo.tbl_Stats_Calls.callDate
>>>
>>>


>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> >-----Original Message-----
>>> >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 =3D=20
dbo.tbl_Stats_Calls.userID
>>> AND
>>> >dbo.tbl_Stats_Saves.saveDate =3D
>>> dbo.tbl_Stats_Calls.callDate
>>> >

AND

>>> >
>>> >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

>>> >
>>> >If anyone has any suggestions or ideas that may
>assist, I
>>> >would be most appreciative. Thank you for your time.
>>> >
>>> >Regards,
>>> >Jeff
>>> >
>>> >.
>>> >
>>
>>
>>.
>>
>
>
>.
>
 
 
Scott





PostPosted: Mon Jun 28 14:16:50 CDT 2004 Top

SQL Server Developer >> OUTER JOIN Problem 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!



> 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
>


>
> 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

>
> If anyone has any suggestions or ideas that may assist, I
> would be most appreciative. Thank you for your time.
>
> Regards,
> Jeff
>


 
 
Jeff





PostPosted: Mon Jun 28 14:56:51 CDT 2004 Top

SQL Server Developer >> OUTER JOIN Problem 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!
>


>> 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
>>


>>
>> 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

>>
>> If anyone has any suggestions or ideas that may assist,
I
>> would be most appreciative. Thank you for your time.
>>
>> Regards,
>> Jeff
>>
>
>
>.
>
 
 
Anith





PostPosted: Mon Jun 28 14:57:38 CDT 2004 Top

SQL Server Developer >> OUTER JOIN Problem >> I'm not hitting on you, but you are a beautiful, beautiful man. <<

Oh Boy !

--
Anith