not exist in place of outer join

[dennishancy]

I have this sql statement:

select A.field1,
B.field2
from table1 A,
table2 B
where A.field1=B.field2
If there is no match, I still want to produce a line of output with
field2 showing a blank. Ordinarily I could simply do an outer join.
But because I have more than two tables in my real query, it won't let
me.
So instead, I tried this approach:
select A.field1,
B.field2
from table1 A,
table2 B
where A.field1=B.field2
or not exist
(select 1 from table2 C where C.field2 = A.field1)

But now I get this error:
--------------
Incorrect syntax near the keyword 'select'. Incorrect syntax near ')'.
Statement(s) could not be prepared.
--------------

Any hints as to what I need to fix?

Thanks.
Dennis Hancy
Eaton Corporation
Cleveland, OH

[Simon Hayes]

It's NOT EXISTS (note the final 'S'). But in any case, you can use
outer joins with any number of tables in the join - if you post a more
complete query, and explain what you mean by "it won't let me" (error,
unexpected results etc.), then someone may be able to help you with it.

Simon

[David Portas]

> Ordinarily I could simply do an outer join.

But because I have more than two tables in my real query, it won't let me.

What "won't let you"? Please try to explain your requirements fully:
http://www.aspfaq.com/etiquette.asp?id=5006

The error in your code is due to the typo: EXIST instead of EXISTS

--
David Portas
SQL Server MVP
--

[dennishancy]

Using MS Query..

If I attempt an outer join, it results in an error message: Cannot have
an outer join if the query has more than two tables.
The existS solution worked great. Thanks.

Any idea how I get around the outer join problem though.

[David Portas]

This must be a limitation of the version of Query you are using. There
is no such limitation built into SQL Server. Can you use Query Analyzer
instead and then paste the query to where you need it?

--
David Portas
SQL Server MVP
--

[dennishancy]

Where do I get Query Analyzer from?

This "not exists" solution is giving me strange results. It is
including all records from table1, as I wanted; however, I would expect
that the field2 value would be blank in those cases where there was no
match in table2. Instead, I am getting values in there (which look
like they are coming from other records).

I am so confused. Can anyone figure out why this is happening?
Dennis

[Erland Sommarskog]

(de*********@eaton.com) writes:

Where do I get Query Analyzer from?

Query Analyser comes with all editions of SQL Server save for MSDE.

If you are using MSDE, you can try OSQL which you can run from a
command-line window. You could put your query in a file, and run
that file with:

osql -E -S server -n -i yourfile.sql

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp