LEFT OUTER JOIN - Help Needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

LEFT OUTER JOIN - Help Needed

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2010-05-28 : 14:35:55

I'm having troube with the 2nd part of my where clause, it doesnt seem to return any records (1st part does)

FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.ID = t2.ID
WHERE (t1.CreatedOn BETWEEN '05/01/2010' AND '05/08/2010')
OR (t2.ModifiedOn BETWEEN '05/01/2010' AND '05/08/2010')

This query cannot use a UNION. Though a UNION is fit perfectally.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-28 : 14:40:06

Maybe you need to move the second part to the join condition. Read this for more information: http://www.sqlteam.com/article/additional-criteria-in-the-join-clause

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2010-05-28 : 14:42:53

Can I do a ON t1.ID = t2.ID OR 1 = 1?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-28 : 14:44:55

quote:
Originally posted by tmaiden

Can I do a ON t1.ID = t2.ID OR 1 = 1?

You haven't provided enough information to be able to help you. We would need to see sample data such as matching rows in both tables, rows that only exist in the LEFT table, and also rows that should be in your result set but aren't.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2010-05-28 : 14:50:06

Typo

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2010-05-28 : 14:51:08

ERRR.

Table1 has a CreatedOn & ModifiedOn Date
Table2 has a CreatedOn & ModifiedOn Date

I would like to accomplish this
SELECT B.field FROM Base B
WHERE B.CreatedOn = '05/01/2010'
UNION
SELECT B.field FROM Base B
INNER JOIN Table2 t2 ON t2.ID = B.ID
WHERE t2.ModifiedOn = '05/01/2010'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-28 : 15:39:14

Still waiting on that sample data...

If you have a working query with UNION, why can't that be used?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2010-06-01 : 12:48:10

Cannot use the UNION query since I will be creating this as a view.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-01 : 13:04:16

UNION works fine in a view.

Here's an example:

create table table1 (column1 int)
create table table2 (column1 int)
go
create view view1
as
select column1 from table1
union
select column1 from table2
go
insert into table1 values(1)
insert into table2 values(2)
go
select * from view1
go
drop view view1
drop table table1, table2
go

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-01 : 13:54:11

You still haven't shown us sample data. Rather than opening a new topic on your issue, let's use this one. You need to provide sample data in order for us to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2010-06-01 : 14:01:28

Sorry I don't really understand the need to see the data. I'll create a variable table to include data and post when Im done.

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2010-06-01 : 14:12:52

DECLARE @Base TABLE (ID INT, CreatedOn DATETIME, Filed VARCHAR(40))
INSERT INTO @Base VALUES (1, '05/09/2010', 'TEXT')
INSERT INTO @Base VALUES (2, '05/09/2010', 'TEXT')
INSERT INTO @Base VALUES (3, '05/09/2010', 'TEXT')
INSERT INTO @Base VALUES (4, '05/09/2010', 'TEXT')
INSERT INTO @Base VALUES (5, '05/10/2010', 'TEXT')
INSERT INTO @Base VALUES (6, '05/10/2010', 'TEXT')
INSERT INTO @Base VALUES (7, '05/10/2010', 'TEXT')
INSERT INTO @Base VALUES (8, '05/11/2010', 'TEXT')
INSERT INTO @Base VALUES (9, '05/12/2010', 'TEXT')
INSERT INTO @Base VALUES (10, '05/13/2010', 'TEXT')

DECLARE @Table1 TABLE (ID INT, ModifiedOn DATETIME, Filed VARCHAR(40))
INSERT INTO @Table1 VALUES (1, '05/10/2010', 'TEXT')
INSERT INTO @Table1 VALUES (2, '05/10/2010', 'TEXT')
INSERT INTO @Table1 VALUES (3, '05/10/2010', 'TEXT')
INSERT INTO @Table1 VALUES (4, '05/10/2010', 'TEXT')
INSERT INTO @Table1 VALUES (5, '05/11/2010', 'TEXT')
INSERT INTO @Table1 VALUES (6, '05/11/2010', 'TEXT')
INSERT INTO @Table1 VALUES (7, '05/11/2010', 'TEXT')
INSERT INTO @Table1 VALUES (8, '05/11/2010', 'TEXT')
INSERT INTO @Table1 VALUES (9, '05/11/2010', 'TEXT')
INSERT INTO @Table1 VALUES (10, '05/10/2010', 'TEXT')

-- 7 ROWS FROM BASE
SELECT *
FROM @Base B
WHERE B.CreatedOn BETWEEN '05/09/2010' AND '05/10/2010'

-- 5 ROWS FROM TABLE1
SELECT T1.*
FROM @Base B
INNER JOIN @Table1 T1
ON T1.ID = B.ID
WHERE T1.ModifiedOn BETWEEN '05/09/2010' AND '05/10/2010'

-- 12 ROWS FROM BOTH
SELECT *
FROM @Base B
FULL OUTER JOIN @Table1 T1
ON T1.ID = B.ID
WHERE B.CreatedOn BETWEEN '05/09/2010' AND '05/10/2010'
OR T1.ModifiedOn BETWEEN '05/09/2010' AND '05/10/2010'

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2010-06-01 : 14:13:36

THE 12 ROWS QUERY IS WHAT I NEED

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2010-06-01 : 14:18:10

THIS MIGHT BE CORRECT.... CHANGED SOME VALUES

DECLARE @Base TABLE (ID INT, CreatedOn DATETIME, Filed VARCHAR(40))
INSERT INTO @Base VALUES (1, '05/09/2010', 'TEXT')
INSERT INTO @Base VALUES (2, '05/09/2010', 'TEXT')
INSERT INTO @Base VALUES (3, '05/09/2010', 'TEXT')
INSERT INTO @Base VALUES (4, '05/09/2010', 'TEXT')
INSERT INTO @Base VALUES (5, '05/10/2010', 'TEXT')
INSERT INTO @Base VALUES (6, '05/10/2010', 'TEXT')
INSERT INTO @Base VALUES (7, '05/10/2010', 'TEXT')
INSERT INTO @Base VALUES (8, '05/11/2010', 'TEXT')
INSERT INTO @Base VALUES (9, '05/12/2010', 'TEXT')
INSERT INTO @Base VALUES (10, '05/13/2010', 'TEXT')

DECLARE @Table1 TABLE (ID INT, ModifiedOn DATETIME, Filed VARCHAR(40))
INSERT INTO @Table1 VALUES (1, '05/10/2010', 'TEXT')
INSERT INTO @Table1 VALUES (12, '05/10/2010', 'TEXT')
INSERT INTO @Table1 VALUES (13, '05/10/2010', 'TEXT')
INSERT INTO @Table1 VALUES (4, '05/10/2010', 'TEXT')
INSERT INTO @Table1 VALUES (5, '05/11/2010', 'TEXT')
INSERT INTO @Table1 VALUES (6, '05/11/2010', 'TEXT')
INSERT INTO @Table1 VALUES (7, '05/11/2010', 'TEXT')
INSERT INTO @Table1 VALUES (8, '05/11/2010', 'TEXT')
INSERT INTO @Table1 VALUES (9, '05/11/2010', 'TEXT')
INSERT INTO @Table1 VALUES (10, '05/10/2010', 'TEXT')

-- 7 ROWS FROM BASE
SELECT *
FROM @Base B
WHERE B.CreatedOn BETWEEN '05/09/2010' AND '05/10/2010'

-- 3 ROWS FROM TABLE1
SELECT T1.*
FROM @Base B
RIGHT OUTER JOIN @Table1 T1
ON T1.ID = B.ID
WHERE T1.ModifiedOn BETWEEN '05/09/2010' AND '05/10/2010'

-- 10 ROWS FROM BOTH
SELECT ISNULL(B.ID,T1.ID), B.CreatedOn, T1.ModifiedOn
FROM @Base B
FULL OUTER JOIN @Table1 T1
ON T1.ID = B.ID
WHERE B.CreatedOn BETWEEN '05/09/2010' AND '05/10/2010'
OR T1.ModifiedOn BETWEEN '05/09/2010' AND '05/10/2010'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-01 : 14:32:40

Could you also post the output that the select needs to return? The sample data and the output will help us duplicate your issue in our own environment.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources