Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-07-20
11
Medium Priority
?
737 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 143

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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
 

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 1000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

885 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