Solved

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

Posted on 2006-07-20
11
721 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
Comment Utility
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]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
If you want to rewrite the first query, then use INNER JOIN in your second query as apresto suggested.
0
 

Author Comment

by:anwarmir
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 87
SQL Server memory Issue 7 73
Report Builder 9 24
Mssql SQL query 14 24
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

10 Experts available now in Live!

Get 1:1 Help Now