Solved

Query contains an illegal outer-join request

Posted on 1998-11-10
4
1,027 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
ID: 1091202
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
ID: 1091203
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
ID: 1091204
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
ID: 1091205
The left join did the trick.  Thanks!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Server Management Studio, Save query 2 18
cannot connect to sqlserver 8 27
SQL SELECT query help 7 34
sql server service accounts 4 21
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

776 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