Solved

Different results with LEFT Outer join usin ANSI and Non ANsi Joins

Posted on 2006-07-20
11
723 Views
Last Modified: 2008-02-01
Hi I have two sql statements: one using ANSI and the other non ansi. Both bring back diff results on SQL 2000

select t1.iWindowTag, t2.iWindowTag, t2.iPermissions, t2.chGroupId from prcWindows t1, prcWinPermissions t2
where t2.chGroupId = 'salesexec'
and t1.iWindowTag *= t2.iWindowTag

select t1.iWindowTag, t2.iPermissions, t2.chGroupId
    from prcWindows t1 left outer join prcWinPermissions t2 on t1.iWindowTag = t2.iWindowTag
where t2.chGroupId = 'salesexec'

chGroupId  does not exist in prcWinPermissions. Can ayone help me making the results from both consistent . Thanks



0
Comment
Question by:anwarmir
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 23

Expert Comment

by:apresto
ID: 17144761
Hi anwarmir,

The first query would act as an inner join whereas the second is a left join.

Inner Joins return only records that from the first table if they exist in the second
Left Joins return records from the second table ( as null ) even if they dont exist in the first

Apresto
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17144762
dont use the ANSI syntax, stick with the LEFT JOIN...

anyhow:


select t1.iWindowTag, t2.iWindowTag, t2.iPermissions, t2.chGroupId from prcWindows t1, prcWinPermissions t2
where 'salesexec' *= t2.chGroupId
and t1.iWindowTag *= t2.iWindowTag
0
 
LVL 23

Expert Comment

by:apresto
ID: 17144763
Try running an INNER JOIN instead of a left join (for your second query) and see if the results are the same
0
 

Author Comment

by:anwarmir
ID: 17144775
I am using the wild card * to denote a left join in the first query this syntax i belive is from way back. So they are bot trying to do a left outer join
0
 

Author Comment

by:anwarmir
ID: 17144782
Using this:

select t1.iWindowTag, t2.iWindowTag, t2.iPermissions, t2.chGroupId from prcWindows t1, prcWinPermissions t2
where 'salesexec' *= t2.chGroupId
and t1.iWindowTag *= t2.iWindowTag

 i get

Server: Msg 150, Level 15, State 1, Line 4
Both terms of an outer join must contain columns.


0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:anwarmir
ID: 17144790
I just need to be sure which one is correct as I want to rewrite the first query using the second but i get differernt results
0
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 17144795
If you want to rewrite the first query, then use INNER JOIN in your second query as apresto suggested.
0
 

Author Comment

by:anwarmir
ID: 17144812
sorry but i'm not quite clear as to why as the first query is doing a left join
0
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 17144820
I don't know either ... if you use t1.xxx *= t2.xxx, other condition should be tested with only fields from t1. Otherwise, it becomes INNER JOIN (t1.xxx = t2.xxx)
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17145666
OK, how about:

select t1.iWindowTag, t2.iWindowTag, t2.iPermissions, t2.chGroupId
from prcWindows t1, prcWinPermissions t2
where t1.iWindowTag *= t2.iWindowTag
and (t2.chGroupId = 'salesexec' or t2.chGroupId is null)
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 17146303
select t1.iWindowTag, t2.iPermissions, t2.chGroupId
    from prcWindows t1 left outer join prcWinPermissions t2 on t1.iWindowTag = t2.iWindowTag
where t2.chGroupId = 'salesexec'

in the above ...
you are joining t1 and t2 on the iwindowtag condition.....
if a particular windowtag doesn't exist on T2 then you'll still get the t1 rows ....

however because you have the where condition t2.chGroupId = 'salesexec'
you are requiring all rows to satisfy that condition....

so either specify

select t1.iWindowTag, t2.iPermissions, t2.chGroupId
    from prcWindows t1 left outer join prcWinPermissions t2 on t1.iWindowTag = t2.iWindowTag
and t2.chGroupId = 'salesexec'

or
select t1.iWindowTag, t2.iPermissions, t2.chGroupId
    from prcWindows t1
left outer join (select * from prcWinPermissions where t2.chGroupId = 'salesexec') as t2
on t1.iWindowTag = t2.iWindowTag

ie so that the Joined table has the selection included within it....

so that the null join conditions are allowed to be shown...

hth


0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now