outer join

Select * from table1 (not table2

Post by iccsi
I have following tables and data and would like to have a out join,
but for some reason it does not work.
table1
CalDate
1/1/2012
1/2/2012
1/3/2012
table2
ADate, MyValue
1/2/2012 1
1/3/2012 2
I want to have following result
1/1/2012 0
1/2/2012 1
1/3/2012 2
I tried to use left outer joint, right outer join and full outer join.
the result only to have 1/2/2012 and 1/3/2012 which only has table2
data.
My SQL as following
select * from table2
left outer join table1 on table1.caldate = table2.adate
select * from table2
right outer join table1 on table1.caldate = table2.adate
select * from table2
full outer join table1 on table1.caldate = table2.adate
I must have something wrong for the outer join query.
If I understand correctly, the left outer join should have all left
table records.
Your help is great appreciated,
iccsi

iccsi

2012-01-17 21:08:23 UTC

Select * from table1 (not table2

- Show quoted text -

same thing, I only got the table2 value, but not table1 date.
Thanks again,

iccsi

iccsi

2012-01-17 21:23:43 UTC

Select * from table1 (not table2

- Show quoted text -

is it possible that the datetime type field issue?
Thanks again,

iccsi

Phil Hunt

2012-01-17 22:17:13 UTC

Maybe the date field is really a datetime field that contains time. And you
are just showing us the date.
That would explain why it dose not match up.

Select * from table1 (not table2

- Show quoted text -

is it possible that the datetime type field issue?
Thanks again,

iccsi

Bob Barrows

2012-01-17 21:31:27 UTC

select t1.Caldate, t2.myvalue
from table1 t1 left join table2 t2 on t1.caldate=t2.adate

Erland Sommarskog

2012-01-17 22:10:33 UTC

Apparently your real query looks different. This works just like you want:

CREATE TABLE #caldate (d date NOT NULL)
INSERT #caldate(d)
VALUES ('2012-01-01'), ('2012-02-01'), ('2012-03-01')
go
CREATE TABLE #othertable (d date NOT NULL,
v int NOT NULL)
INSERT #othertable(d, v)
VALUES ('2012-02-01', 1), ('2012-03-01', 2)
go
SELECT c.d, coalesce(o.v, 0)
FROM #caldate c
LEFT JOIN #othertable o ON c.d = o.d
go
DROP TABLE #caldate, #othertable

iccsi

2012-01-17 23:56:51 UTC

Post by Erland Sommarskog
CREATE TABLE #caldate (d date NOT NULL)
INSERT #caldate(d)
VALUES ('2012-01-01'), ('2012-02-01'), ('2012-03-01')
go
CREATE TABLE #othertable (d date NOT NULL,
v int NOT NULL)
INSERT #othertable(d, v)
VALUES ('2012-02-01', 1), ('2012-03-01', 2)
go
SELECT c.d, coalesce(o.v, 0)
FROM #caldate c
LEFT JOIN #othertable o ON c.d = o.d
go
DROP TABLE #caldate, #othertable

Thanks for helping,
I just realized that the table2 is composite key.
I need use

select * from table2
left outer join table1 on table1.caldate = table2.adate and
table1.key1 = table2.key2

I did not have the key field list.

this is the issue,
Thanks again for helping,

iccsi

iccsi

2012-01-18 17:54:01 UTC

I have following table and data

create table #wc
( CalDate smallDateTime,
EmployeeID int )

create table #warcm
( ActivityDate smallDateTime,
Employee_ID int,
MyValue int )

data in #wc are

CalDate EmployeeID

1/1/2012 3
1/2/2012 3

data in #warcm are

ActivityDate EmployeeID MyValue
1/2/2012 3 1

select #wc.caldate, #warcm.MyValue
from #warcm
right outer join #wc on #warcm.Activitydate = #wc.Caldate
and #warcm.Employee_ID = #wc.EmployeeID

I use above SQL, but it does not show 1/1/2012 in #wc with null
myvalue.
I need the result set to return 1/1/2012 with employee 3 and myvalue
is null.
I am not sure where is the problem.

I thought above SQL will show all the records in right table.

Your help is great appreciated,

iccsi

Bob Barrows

2012-01-18 20:15:27 UTC

Post by iccsi
I have following table and data
create table #wc
( CalDate smallDateTime,
EmployeeID int )
create table #warcm
( ActivityDate smallDateTime,
Employee_ID int,
MyValue int )
data in #wc are
CalDate EmployeeID
1/1/2012 3
1/2/2012 3
data in #warcm are
ActivityDate EmployeeID MyValue
1/2/2012 3 1
select #wc.caldate, #warcm.MyValue
from #warcm
right outer join #wc on #warcm.Activitydate = #wc.Caldate
and #warcm.Employee_ID = #wc.EmployeeID
I use above SQL, but it does not show 1/1/2012 in #wc with null
myvalue.

It does when I run it. I copied and pasted it into SSMS and ran it without
modification (even though I would have written it as a left join rather than
right - why do you insist on putting the "outer" table first?)

Post by iccsi
I need the result set to return 1/1/2012 with employee 3 and myvalue
is null.
I am not sure where is the problem.

Neither am I - it works correctly on my machine:

SELECT CAST('20120101' AS smallDATETIME) Caldate,3 EmployeeID INTO #wc
UNION ALL
SELECT '20120102',3;

SELECT CAST('20120102' AS smallDATETIME) ActivityDate,3 Employee_ID, 1
MyValue INTO #warcm;

select #wc.caldate, #warcm.MyValue
from #warcm
right outer join #wc on #warcm.Activitydate = #wc.Caldate
and #warcm.Employee_ID = #wc.EmployeeID

Results:
caldate MyValue
2012-01-01 00:00:00 NULL
2012-01-02 00:00:00 1

iccsi

2012-01-18 20:51:42 UTC

Post by iccsi
I need the result set to return 1/1/2012 with employee 3 and myvalue
is null.
I am not sure where is the problem.

SELECT CAST('20120101' AS smallDATETIME) Caldate,3 EmployeeID INTO #wc
UNION ALL
SELECT '20120102',3;
SELECT CAST('20120102' AS smallDATETIME) ActivityDate,3 Employee_ID, 1
MyValue INTO #warcm;
select #wc.caldate, #warcm.MyValue
from #warcm
right outer join #wc on #warcm.Activitydate = #wc.Caldate
and #warcm.Employee_ID = #wc.EmployeeID
caldate MyValue
2012-01-01 00:00:00 NULL
2012-01-02 00:00:00 1- Hide quoted text -
- Show quoted text -

Thanks a milion,
I just tested left and right outer join to try to make it work.
I am glad that I have theory right.
I use SQL Sever 2000 and SQL Analyzer.
Is it possible SQL Server 2000 bug?

Since it does not work on my SQL server 2000 for some unknown reason,
I tired to use not exists query and union 2 queries which works to get
the result I am looking for for me for now.

It is still a mystery that outer join does not work on my SQL server
2000 for some reason. It must be some reason that I am not aware of.

Thanks again,

iccsi

Bob Barrows

2012-01-18 21:06:23 UTC

Post by iccsi
Thanks a milion,
I just tested left and right outer join to try to make it work.
I am glad that I have theory right.
I use SQL Sever 2000 and SQL Analyzer.
Is it possible SQL Server 2000 bug?

I don't think so - I just checked with QA on a SQL 2000 server, and it works
perfectly.

What is the collation of your server?
What SP is your server at?

iccsi

2012-01-18 21:35:18 UTC

I don't think so - I just checked with QA on a SQL 2000 server, and it works
perfectly.
What is the collation of your server?
What SP is your server at?

what can be wrong?
How can I get collation and SP information for the server?

Thanks again,

iccsi

Bob Barrows

2012-01-19 00:54:19 UTC

I don't think so - I just checked with QA on a SQL 2000 server, and
it works perfectly.
What is the collation of your server?
What SP is your server at?

what can be wrong?
How can I get collation and SP information for the server?

I simply right-clicked the server name in EM and looked at the properties
dialog ...

iccsi

2012-01-19 16:58:32 UTC

I don't think so - I just checked with QA on a SQL 2000 server, and
it works perfectly.
What is the collation of your server?
What SP is your server at?

what can be wrong?
How can I get collation and SP information for the server?

I simply right-clicked the server name in EM and looked at the properties
dialog ...- Hide quoted text -
- Show quoted text -

I could not find the information you mentioned.
Can you please let me know the information on which tab?
Thanks again,

iccsi

Bob Barrows

2012-01-19 18:08:24 UTC

I don't think so - I just checked with QA on a SQL 2000 server, and
it works perfectly.
What is the collation of your server?
What SP is your server at?

what can be wrong?
How can I get collation and SP information for the server?

I simply right-clicked the server name in EM and looked at the
properties dialog ...- Hide quoted text -
- Show quoted text -

I could not find the information you mentioned.
Can you please let me know the information on which tab?
Thanks again,

It's right on the General tab of the Properties window ... at least it is on
my machine. "Product version" and "Server collation".
You can also use T-SQL to get this info:
SELECT @@VERSION
SELECT SERVERPROPERTY('collation')

iccsi, you really need to start developing or honing your google skills

iccsi

2012-01-19 18:18:05 UTC

I don't think so - I just checked with QA on a SQL 2000 server, and
it works perfectly.
What is the collation of your server?
What SP is your server at?

what can be wrong?
How can I get collation and SP information for the server?

I simply right-clicked the server name in EM and looked at the
properties dialog ...- Hide quoted text -
- Show quoted text -

I could not find the information you mentioned.
Can you please let me know the information on which tab?
Thanks again,

It's right on the General tab of the Properties window ... at least it is on
my machine. "Product version" and "Server collation".
SELECT SERVERPROPERTY('collation')
iccsi, you really need to start developing or honing your google skills- Hide quoted text -
- Show quoted text -

Product version 8.00.760 (SP3)
Server Collation SQL_Latin1_General_CP1_CI_AS

Bob Barrows

2012-01-19 19:42:08 UTC

I don't think so - I just checked with QA on a SQL 2000 server,
and it works perfectly.
What is the collation of your server?
What SP is your server at?

what can be wrong?
How can I get collation and SP information for the server?

I simply right-clicked the server name in EM and looked at the
properties dialog ...- Hide quoted text -
- Show quoted text -

I could not find the information you mentioned.
Can you please let me know the information on which tab?
Thanks again,

It's right on the General tab of the Properties window ... at least it is on
my machine. "Product version" and "Server collation".
SELECT SERVERPROPERTY('collation')

Product version 8.00.760 (SP3)
Server Collation SQL_Latin1_General_CP1_CI_AS

Same as mine - that's not the problem. I'm at a loss. Sorry.

iccsi

2012-01-18 21:31:43 UTC

I don't think so - I just checked with QA on a SQL 2000 server, and it works
perfectly.
What is the collation of your server?
What SP is your server at?

how can I get collation and SP information for the server?
Thanks again,

iccsi

Erland Sommarskog

2012-01-18 22:13:27 UTC

Post by iccsi
select #wc.caldate, #warcm.MyValue
from #warcm
right outer join #wc on #warcm.Activitydate = #wc.Caldate
and #warcm.Employee_ID = #wc.EmployeeID
I use above SQL, but it does not show 1/1/2012 in #wc with null myvalue.
I need the result set to return 1/1/2012 with employee 3 and myvalue is
null. I am not sure where is the problem.

I guess that there is a WHERE clause that does not show us. If you do:

select #wc.caldate, #warcm.MyValue
from #warcm
right outer join #wc on #warcm.Activitydate = #wc.Caldate
and #warcm.Employee_ID = #wc.EmployeeID
where #warcm.MyValue > 0

You have in practice converted the outer join to an inner join, because you
are filtering the rows where MyValue is NULL.

If this does not fit the bill, please post a complete script with CREATE
TABLE and INSERT statements that you have tested and that demonstrates the
problem.

--
Erland Sommarskog, SQL Server MVP, ***@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

iccsi

2012-01-19 16:57:47 UTC

select #wc.caldate, #warcm.MyValue
from #warcm
right outer join #wc on #warcm.Activitydate = #wc.Caldate
and #warcm.Employee_ID = #wc.EmployeeID
where #warcm.MyValue > 0
You have in practice converted the outer join to an inner join, because you
are filtering the rows where MyValue is NULL.
If this does not fit the bill, please post a complete script with CREATE
TABLE and INSERT statements that you have tested and that demonstrates the
problem.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

CREATE PROCEDURE spercActivitySummary

@StartDate as Datetime = null,
@EndDate as Datetime = null

AS
create table #wc
( CalDate smallDateTime,
EmployeeID int )

insert into #wc (CalDate, EmployeeID)
select caldate, 3 from tblCalendar
where caldate between @StartDate and @EndDate and workingday =
'Y'

SELECT 1 as MyID, ActivityDate , tblEmployee.Abbreviation as EDA,
SiteRCM as MyValue, 'Site RCM' as MyCode,
tblEmployee.Employee_ID
into #warcm
from tblActivity
INNER JOIN tblEmployee on tblActivity.EDA_ID =
tblEmployee.Employee_ID

WHERE ActivityDate between @StartDate and @EndDate
order by tblEmployee.Abbreviation, ActivityDate

select #warcm.MyID, #wc.caldate, #warcm.EDA,
#warcm.MyValue, #warcm.MyCode from #wc
right outer join #warcm on #warcm.Activitydate = #wc.Caldate
and #warcm.Employee_ID = #wc.EmployeeID
order by eda

drop table #wc
drop table #warcm

here is my stored procedure,
I use the table in the database, there is no insert in the stored
procedures.
For some reason, the result data does not include all date in the #wc.

Thanks again for helping,

iccsi

Erland Sommarskog

2012-01-19 22:18:14 UTC

Post by iccsi
select #warcm.MyID, #wc.caldate, #warcm.EDA,
#warcm.MyValue, #warcm.MyCode from #wc
right outer join #warcm on #warcm.Activitydate = #wc.Caldate
and #warcm.Employee_ID = #wc.EmployeeID
order by eda
For some reason, the result data does not include all date in the #wc.

First of always use LEFT JOIN. RIGHT JOIN makes my head hurt.

A RIGHT JOIN can always be rewritten as a LEFT JOIN:

SELECT #warcm.MyID, #wc.caldate, #warcm.EDA,
#warcm.MyValue, #warcm.MyCode
FROM #warcm
LEFT JOIN #wc ON #warcm.Activitydate = #wc.Caldate
AND #warcm.Employee_ID = #wc.EmployeeID
ORDER BY #wcwarm.eda

I've also changed formatting to the make the query more readable.

Since #wc is the outer table in the query, there is no reason to expect
all rows from this table to appear in the query. I don't know what you
want to achieve, but maybe it is as simple as

SELECT #warcm.MyID, #wc.caldate, #warcm.EDA,
#warcm.MyValue, #warcm.MyCode
FROM #wc
LEFT JOIN #warcm ON #warcm.Activitydate = #wc.Caldate
AND #warcm.Employee_ID = #wc.EmployeeID
ORDER BY #wcwarm.eda

Or why not:

SELECT 1 AS MyID, C.CalDate, E.Abbreviation as EDA,
A.SiteRCM AS MyValue, 'Site RCM' AS MyCode E.Employee_ID
FROM tblCalendar C
LEFT JOIN (tblActivity A
JOIN tblEmployee E ON E.EDA_ID = E.Employee_ID)
ON C.CalDate = A.ActivityDate
WHERE C.Caldate BETWEEN @StartDate AND @EndDate
AND C.Workingdate = 'Y'

And scrap the temp tables entirely.

iccsi

2012-01-22 00:46:25 UTC