Left outer join? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Left outer join?

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-03 : 06:38:46

Hi,

I thought left outer join was supposed to bring back what is not in the join?

A Left outer join B, brings back everything in A thats not in B?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 06:40:54

only if you also provide a
where b.col1 is null

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 06:46:56

quote:
Originally posted by cipriani1984

Hi,

I thought left outer join was supposed to bring back what is not in the join?

A Left outer join B, brings back everything in A thats not in B?

thats except

A EXCEPT B

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-03 : 06:47:57

This is my query, can you give me an example of the null clause you were referring to?

SELECT datesregisters.register_id, datesregisters.register_group, datesregisters.WeekNo, datesregisters.Day, datesregisters.datenew
FROM datesregisters INNER JOIN registers_view ON
datesregisters.register_id = registers_view.register_id AND
datesregisters.register_group = registers_view.register_group AND
datesregisters.WeekNo = registers_view.week_no AND
datesregisters.Day = registers_view.day_of_week
GROUP BY datesregisters.register_id, datesregisters.register_group,
datesregisters.WeekNo, datesregisters.Day, datesregisters.datenew
HAVING (datesregisters.datenew < GETDATE())
ORDER BY datesregisters.register_id, datesregisters.register_group,
datesregisters.WeekNo, datesregisters.Day

quote:
Originally posted by Peso

only if you also provide a
where b.col1 is null

E 12°55'05.63"
N 56°04'39.26"

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-03 : 06:48:43

Sorry ignore INNER join i knocked that up quickly as example. Using Left outer join.

quote:
Originally posted by cipriani1984

This is my query, can you give me an example of the null clause you were referring to?

SELECT datesregisters.register_id, datesregisters.register_group, datesregisters.WeekNo, datesregisters.Day, datesregisters.datenew
FROM datesregisters INNER JOIN registers_view ON
datesregisters.register_id = registers_view.register_id AND
datesregisters.register_group = registers_view.register_group AND
datesregisters.WeekNo = registers_view.week_no AND
datesregisters.Day = registers_view.day_of_week
GROUP BY datesregisters.register_id, datesregisters.register_group,
datesregisters.WeekNo, datesregisters.Day, datesregisters.datenew
HAVING (datesregisters.datenew < GETDATE())
ORDER BY datesregisters.register_id, datesregisters.register_group,
datesregisters.WeekNo, datesregisters.Day

quote:
Originally posted by Peso

only if you also provide a
where b.col1 is null

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 06:51:26

[code]SELECT datesregisters.register_id, datesregisters.register_group, datesregisters.WeekNo, datesregisters.Day, datesregisters.datenew
FROM datesregisters LEFT JOIN registers_view ON
datesregisters.register_id = registers_view.register_id AND
datesregisters.register_group = registers_view.register_group AND
datesregisters.WeekNo = registers_view.week_no AND
datesregisters.Day = registers_view.day_of_week
WHERE (datesregisters.datenew < GETDATE())
AND registers_view.register_id IS NULL
GROUP BY datesregisters.register_id, datesregisters.register_group,
datesregisters.WeekNo, datesregisters.Day, datesregisters.datenew
ORDER BY datesregisters.register_id, datesregisters.register_group,
datesregisters.WeekNo, datesregisters.Day[/code]

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-03 : 07:00:47

Thank you!

quote:

Originally posted by visakh16

SELECT datesregisters.register_id, datesregisters.register_group, datesregisters.WeekNo, datesregisters.Day, datesregisters.datenew
FROM datesregisters LEFT JOIN registers_view ON 
datesregisters.register_id = registers_view.register_id AND 
datesregisters.register_group = registers_view.register_group AND 
datesregisters.WeekNo = registers_view.week_no AND 
datesregisters.Day = registers_view.day_of_week
WHERE (datesregisters.datenew < GETDATE())
AND registers_view.register_id IS NULL
GROUP BY datesregisters.register_id, datesregisters.register_group, 
datesregisters.WeekNo, datesregisters.Day, datesregisters.datenew
ORDER BY datesregisters.register_id, datesregisters.register_group, 
datesregisters.WeekNo, datesregisters.Day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 07:20:43

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources