Right 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

General SQL Server Forums

New to SQL Server Programming

Right Join

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-19 : 12:18:50

I thought if I use a right join then the table to the right will return all records if there is a match or not. This query isn’t giving me that. What am I doing wrong?

select n.doc, isnull(count(t.cossn),0) as PendingInFOandDDS
from t16pendall t
 right join natdocfile n on n.doc = t.fo 
where mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y')
group by  doc
order by doc

This query produces this:
doc         PendingInFOandDDS
100             1
389             1
913 	        1
A00	        5
A38             1
B14             1

This is what I want:

doc         PendingInFOandDDS
001	        0
009             0
00K		0        
00N		0 	        
00U		0
100		1
389		1
913		1	
A00		5
A38		1
B14		1

Here's the table info:



CREATE TABLE [dbo].[natdocfile](
	[doc] [varchar](3) NOT NULL)


insert into natdocfile

select '001' union all
select 'A00' union all
select '389' union all
select 'A38' union all
select '100' union all
select 'B14' union all
select '913' union all
select '009' union all
select '00K' union all
select '00N' union all
select '00U'
 


CREATE TABLE [dbo].[T16pendall](
	[COSSN] [char](9) NOT NULL,
	[FLG_CDT] [datetime] NULL,
	[AGED_ALIEN_RSW] [char](1) NOT NULL,
	[MFT_POSN1_CD] [char](1) NOT NULL,
	[FO] [varchar](4) NOT NULL
) ON [PRIMARY]




insert into T16pendall
select '268763’, '10/6/2010’, ' ', 'D', '389' union all
select '337629', '10/21/2010’, ' ', 'D', 'A00' union all
select '240312', '10/28/2010’, ' ', 'D', '330' union all
select '406339', '10/4/2010’, ' ', 'D', '442' union all
select '279700', '11/1/2010’, ' ', 'D', '387' union all
select '680031', '10/29/2010’, ' ', 'D', 'A00' union all
select '298582', '10/26/2010’, ' ', 'D', '387' union all
select '074506', '9/23/2010’, ' ', 'D', '329' union all
select '610128', '10/6/2010’, ' ', 'A', '893' union all
select '427337', '6/30/2010’, ' ', 'D', 'B14' union all
select '553084', '10/15/2010’, ' ', 'D', '913' union all
select '587962', '8/30/2010’, ' ', 'D', '100' union all
select '746855', '9/10/2010’, ' ', 'D', 'A38' union all
select '429194', '9/13/2010’, ' ', 'D', 'A00' union all
select '424431', '9/14/2010’, ' ', 'D', 'A00' union all
select '150993', '9/15/2010’, ' ', 'D', 'A00'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 12:27:18

Can't rference the optional table in the where clause. The values will be null and it will turn into an inner join
select n.doc, isnull(count(t.cossn),0) as PendingInFOandDDS
from t16pendall t
right join natdocfile n
on n.doc = t.fo
and (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
group by doc
order by doc

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-19 : 12:30:20

see

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 12:38:09

And I never use right join - I find it confusing and stick to left join.
Not saying you're wrong just not like me (which is also wrong :) ).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-19 : 13:29:05

Thanks so much nigelrivett for the solution and visakh16 for the information!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-19 : 14:03:43

Back again...

I want to add this to the query:

and cossn is not = cossin in T16pendmvt

The below query works but want to make sure the query is correct.

select n.doc, count(t.cossn) as PendingInFO1
from t16pendall t
right join natdocfile n
on n.doc = t.fo
and mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y')
and t.cossn not in(select cossn from t16pendmvt)
group by doc
order by doc

Here's the table info for table t16pendmvt


CREATE TABLE [dbo].[T16pendmvt](
	[COSSN] [char](6) NOT NULL,
	[MVT_TYP] [char](1) NOT NULL,
	[MVT_LOC] [char](3) NOT NULL,
	[MVT_DEST] [varchar](3) NOT NULL,
	[MVT_CDT] [datetime] NULL
) ON [PRIMARY]

insert into T16pendmvt
select '074506', 'T', '329', 'S36', '9/23/2009' union all

select '610128', 'R', 'S15', '  ', '10/6/2010' union all

select '427337', 'R', 'S27', ' ', '7/1/2010' union all

select '553084', 'R', 'V64', ' ', '10/26/2010' union all

select '999962', 'T', 'SS2', 'R42', '2/3/2003' union all

select '452262', 'R', 'VS2', '   ', '1/22/2004' union all

select '458962', 'R', 'V36', '   ', '5/5/2004' union all

select '458962', 'R', 'V25', '858', '6/8/2004' union all

select '458962', 'T', 'S72', 'S24', '7/19/2004' union all

select '458962', 'R', '004', ' ', '4/8/2010'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 14:45:14

should be fine

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-19 : 14:52:46

Thanks!

I have a question... how come I don't need the Where clause:

select n.doc, isnull(count(t.cossn),0) as PendingInFOandDDS
from t16pendall t
right join natdocfile n on n.doc = t.fo
where mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y')
group by doc
order by doc

instead you put "and" I'm a bit confused.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 15:07:08

Because it's included in the join.
You can put anything in the join that is in the where clause - it only makes a diference to the result with outer joins.

That doesn't apply to all databases - some only allow columns in the join clause and not literals.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-19 : 15:08:44

In addition to the link posted by Visakh, see if this helps your understanding (Recycling some old electrons):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98481#374748

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-19 : 15:09:24

Okay thanks I understand!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-19 : 15:10:11

Thanks Lamprey I will read this and what Visakh16 sent as well!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-19 : 15:21:48

quote:
Originally posted by Lamprey

In addition to the link posted by Visakh, see if this helps your understanding (Recycling some old electrons):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98481#374748

Good electrons should never be allowed to go to waste!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-19 : 15:25:16

quote:
Originally posted by nigelrivett

Because it's included in the join.
You can put anything in the join that is in the where clause - it only makes a diference to the result with outer joins.

Mostly True. With SQL you need to be careful because it does not honor predicates placed in the join condition that apply to the determinant table on an outer join. (edit: clarification on the join type).

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-19 : 15:27:35

Thanks Russell!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources