Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query contains an illegal outer-join request

Posted on 1998-11-10
4
Medium Priority
?
1,088 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
[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 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 400 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

661 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