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

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



anwarmirAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
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
 
aprestoCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
aprestoCommented:
Try running an INNER JOIN instead of a left join (for your second query) and see if the results are the same
0
 
anwarmirAuthor Commented:
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
 
anwarmirAuthor Commented:
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
 
anwarmirAuthor Commented:
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
 
Mr_PeerapolCommented:
If you want to rewrite the first query, then use INNER JOIN in your second query as apresto suggested.
0
 
anwarmirAuthor Commented:
sorry but i'm not quite clear as to why as the first query is doing a left join
0
 
Mr_PeerapolCommented:
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
 
jrb1Commented:
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
All Courses

From novice to tech pro — start learning today.