Solved

Query contains an illegal outer-join request

Posted on 1998-11-10
4
997 Views
Last Modified: 2008-03-03
Let's say I have table 'a', 'b' and 'c'.

Here is my select statement:

select *
from   a, b, c
where  a.id *= b.id
and    b.id *= c.id

I know it's bad, but is there a way to do it w/o creating
any temporary tables?  I just want to be able to do it in one select statement.
0
Comment
Question by:ivanh
4 Comments
 
LVL 9

Expert Comment

by:david_levine
Comment Utility
Do you mean without MS SQL Server, behind the scenes creating a temporary table? Probably not... but your definitely right... it's a bad thing to do.

Maybe you could tell us what you want in a little more detail. Such as your real table names.

David


0
 
LVL 1

Accepted Solution

by:
kponder earned 100 total points
Comment Utility
This will work if it is truely what you are trying to accomplish. Since you are using b.id in both joins (where clause) changing it to a in the second is a valid operation.

select *
from   a, b, c
where  a.id *= b.id
and    a.id *= c.id


If you did not intend for b.id to be used in both clauses the following example will work

select * from a
  left join b on a.id = b.id
  left join c on b.X = c.id      'substitute x with field name


0
 
LVL 2

Expert Comment

by:aliciaam
Comment Utility
If what you want is to have all the rows this should work

select * from   a
union
select * from  b
union
select * from c

Union is a powerful operator when you request the same information from different tables. You could create a view to hold this information. Let me know if this is what you needed.
0
 
LVL 1

Author Comment

by:ivanh
Comment Utility
The left join did the trick.  Thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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.

744 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

16 Experts available now in Live!

Get 1:1 Help Now