Solved

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

Posted on 2006-07-20
11
732 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

615 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