right 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

General SQL Server Forums

New to SQL Server Programming

right outer join

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-08-01 : 08:22:55

Hi

[/CODE]

I need to use a RIGHT OUTER JOIN instead of *=

but when I try
[CODE]
inner join T1 on T1.category = T2.category
[/CODE]

I get this
Tables or functions 'T1' and 'T1' have the same exposed names. Use correlation names to distinguish them.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-01 : 08:50:05

show us what you tried, coz i'm not convinced it's a right outer join you want, and the error you got suggests you're trying to join T1 to itself somewhere? (which isn't in your original query)

Em

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-08-01 : 09:08:55

So how would I do the equaivalent of
where T1.Category = T2.category

in terms of "inner join " ?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-01 : 09:11:20

do you maybe want something like this...


SELECT 
T1.category as [CATEGORY],
T1.site as [SITE],
T1.agent as [AGENT],
T1.AHT_TOTAL as [AHT_TOTAL(SECONDS)],
T2.AHT_FINISHED as [AHT_FINISHED(SECONDS)],
T3.AHT_Trashed as [AHT_TRASHED(SECONDS)]
FROM T1 
	join T2 
		on T1.category = T2.category
		and T1.site = T2.site
		and T1.agent = T2.agent
	left join T3 
		on T1.category = T3.category
		and T1.site = T3.site
		and T1.agent = T3.agent

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-01 : 09:16:33

my advice:

avoid right outer joins. Always use LEFT outer joins. Much easier to work with and your code is more readable and more clear.

see:

http://weblogs.sqlteam.com/jeffs/archive/2008/02/13/on-right-outer-joins.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-08-01 : 09:18:30

i need a right outer join as sometimes the trashed values will be NULL and i still want to see
the total and finished values in these cases..

or can LEFT OUTER join do that aswell ?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-01 : 09:22:20

that's not a right outer, it's a left. see the query i posted. try it and see if it's what you wanted

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-01 : 09:23:34

actually it might be...


SELECT 
T1.category as [CATEGORY],
T1.site as [SITE],
T1.agent as [AGENT],
T1.AHT_TOTAL as [AHT_TOTAL(SECONDS)],
T2.AHT_FINISHED as [AHT_FINISHED(SECONDS)],
T3.AHT_Trashed as [AHT_TRASHED(SECONDS)]
FROM T1 
	left join T2 
		on T1.category = T2.category
		and T1.site = T2.site
		and T1.agent = T2.agent
	left join T3 
		on T1.category = T3.category
		and T1.site = T3.site
		and T1.agent = T3.agent

but without sample data etc, we're just guessing

Em

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-08-01 : 09:29:39

THANKS .. this seems to be what im looking for ,, im not used to the syntax of using inner left join outer left join etc ..

since i always joined in the where clause...

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-01 : 09:30:36

you'll get used to it

...and we'll help when you get stuck

Em

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-08-01 : 09:39:09

Im a bit confused though, the query works thanks but, shouldn't it say LEFT OUTER JOIN T3, instead of just left join ?

or are they the same (left join = left outer join)

also in my original i needed to link t2 and t3 .. the query above seems not to need to ..

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-01 : 10:02:59

yes,

left join = left outer join

the "outer" is optional.

- Jeff
http://weblogs.sqlteam.com/JeffS

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-01 : 10:33:26

why do you think you're original query needed to join T3 to T1 and T2? if the data that you're joining on is that same in all 3 tables then logically joining T2 and T3 back to T1 will suffice. by adding in the extra join condition between T2 and T3 you are not restricting the resultset any further

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-01 : 10:44:03

dude... why did you take the query out of your original post??

quote:

originally posted query...
SELECT
T1.category as 'CATEGORY',
T1.site as 'SITE',
T1.agent as 'AGENT',
T1.AHT_TOTAL as 'AHT_TOTAL(SECONDS)',
T2.AHT_FINISHED as 'AHT_FINISHED(SECONDS)',
T3.AHT_Trashed as 'AHT_TRASHED(SECONDS)'

FROM T1, T2 ,T3
where
T1.category = T2.category
and T1.category = T3.category
and T2.category *= T3.category
and T1.site = T2.site
and T1.site = T3.site
and T2.site *= T3.site
and T1.agent = T2.agent
and T1.agent = T3.agent
and T2.agent *= T3.agent

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 12:55:30

quote:
Originally posted by pazzy11

THANKS .. this seems to be what im looking for ,, im not used to the syntax of using inner left join outer left join etc ..

since i always joined in the where clause...

you dont have inner left join. you have only left join and left outer join. they both are the same.they just take all data from left table and match with right side on condition specified. they will retrieve the value if they find matches else return null value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources