473,700 Members | 2,427 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Outer Join not working when using SQL-92

All,

I have a perplexing problem that I hope someone can help me with.

I have the following table struct:

Permission
-----------------
PermissionId
Permission
Description

UserPermission
-----------------
PermissionId
UserId
Active

I am attempting to retrieve all records from the permission table
whether there is a match on UserPermission. PermissionId or not.
Therefore I implemented this query, which does not produce the results
that I expect:

SELECT p.Permission,
up.Active

FROM Permission p
LEFT OUTER JOIN UserPermission up
ON p.[Id] = up.PermissionId
WHERE up.UserId = 3
However, if I exec this query, it works as it is supposed to:
SELECT p.Permission,
up.Active

FROM Permission p, UserPermission up
WHERE p.[Id] *= up.PermissionId AND up.UserId = 3

In the first query, only the records that match on "permission Id" are
returned, in the second all records are returned from the Left table
and those records that do not have matching columns are set to null, as
it should be. My question is, what have I done wrong here?

I am running MS-SQLServer 2000

Jul 23 '05 #1
4 4868
The problem is with the way you are using SQL-92 standard.

Instead use the query as follows:
SELECT P.Permission, up.Active
FROM Permission p
LEFT OUTER JOIN UserPermission up
ON p.permissionid = up.PermissionId
AND up.UserId = 3

Regards
Debian


*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #2
Thanks Debian, that did it. Is it then incorrect to use the WHERE
clause when using an outer join? I've looked at books on-line and
could not find a reference to using WHERE clauses with outer joins,
however they appear to be a typical part of inner joins.

Jul 23 '05 #3
Here is a "cut & paste" lecture:

Here is how OUTER JOINs work in SQL-92. Assume you are given:

Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserve d table"
in the query. What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @ = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a b a c
=============== ==========
1 w 1 r @
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *

Table1 LEFT OUTER JOIN Table2
a b a c
=============== ==========
3 y 3 t <= only TRUE row
-----------------------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
-----------------------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
-----------------------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a b a c
=============== ==========
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables

Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250

and let's do an extended equality outer join like this:

SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=============== ========
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
=============== ====
S1 S1 P1 100
S2 S2 P1 100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
=============== ====
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-92.
For example to find the students who have taken Math 101 and might
have taken Math 102:

SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;

Jul 23 '05 #4
thilbert (th******@gmail .com) writes:
Thanks Debian, that did it. Is it then incorrect to use the WHERE
clause when using an outer join? I've looked at books on-line and
could not find a reference to using WHERE clauses with outer joins,
however they appear to be a typical part of inner joins.


Incorrect and incorrect, it has a different meaning. The FROM clause
lasts all the way to WHERE, so you have

FROM a LEFT JOIN b ON a.col = b.col

Then the WHERE is applied. But if you say

WHERE b.othercol = 3

you are effectively filter out the rows from a which did not have a
matching row in b.

If you say

FROM a LEFT JOIN b ON a.col = b.col AND b.othercol = 3

The condition of othercol becomes part of the join, so that for
rows where a.col = b.col but b.othercol = 2 you will get NULL
values for b.*.

You can also say:

FROM a LEFT JOIN b ON a.col = b.col
WHERE b.othercol = 3 OR b.col IS NULL

But this gives a different result. Here the rows where a.col = b.col
but b.othercol = 2 will be removed from the result set.

See also Celko treatise on the subject.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
5947
by: Phil Powell | last post by:
I'm not kidding, the only reason yesterday you didn't hear from me was because I wasn't coding, but today I am doing something quick, and yes, as always it failed.. right at the SQL statement: $sql = 'SELECT v.nnet_produkt_varegruppe_navn, ' . ' f.nnet_produkt_farge_navn, ' . ' s.nnet_produkt_storrelse_navn, ' . ' k.nnet_produkt_kvalitet_navn, ' . ' p.* ' .
9
6406
by: Chris Greening | last post by:
I'm seeing a very strange problem with outer joins. The example below replicates the problem: create table data1 (dim1 integer, stat1 float); create table data2 (dim1 integer, stat2 float); insert into data1 values (1,1); insert into data1 values (1,2); insert into data1 values (2,2);
3
44529
by: mheydman | last post by:
I apologize if this has been asked before- I searched google but could not find a concrete answer. I recently inherited a database whose t-sql code is written in a format that I find difficult to read (versus the format I have used for years). I have tested the queries below using the SQL Profiler, and both have identical costs. Is there any advantage of one format over the other?
2
2238
by: Preston Landers | last post by:
Hello all. I am trying to write a query that "just" switches some data around so it is shown in a slightly different format. I am already able to do what I want in Oracle 8i, but I am having trouble making it work in SQL Server 2000. I am not a database newbie, but I can't seem to figure this one out so I am turning to the newsgroup. I am thinking that some of the SQL Gurus out there have done this very thing a thousand times before...
3
5725
by: dumbledad | last post by:
Hi All, I'm confused by how to replace a SELECT statement in a SQL statement with a specific value. The table I'm working on is a list of words (a column called "word") with an index int pointing to the sentence they come from (a column called "regret"). I also have a table of stop words (called "GenericStopWords") that contains the words I do not want to consider. That table has a single column called "word". I started off using a...
1
4214
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
7
31557
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
3
23097
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but he doesn't know how to do that, or even if he can, and i don't have to time to learn DB2 from scratch right now. The following SQL Query is a trimmed sample of the full View (i.e. Logical) definition - and i would create it on an SQL based...
2
1807
by: Notgiven | last post by:
I have three tables: table1: table2_ID table3_ID complete table3: table3_ID name
4
1571
by: Brian Parker | last post by:
I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN working on a three table query. Assumptions: -- I have events in the Event table. -- Each event CAN have one Transaction, but it's not guaranteed -- Each transaction, ir present, will have one or more Amount records This would be the pseudo-query without any special joins:
0
8648
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9218
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9081
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8975
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8929
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5904
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4660
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3095
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2395
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.