ANSI-89 DB JOINS

Help...I have a DB I'm working with that I know doesn't work with the
ANSI-92 JOIN SYNTAX...I'm not sure how much this limits my ability to
deal with the following situation, so I'm soliciting the help of a
guru...I apologize for the lack of scripted table structure, but this
database is embedded in an application that I have no true schema for.
I have a crude diagram of the tables and some of the relationships, but
I've managed to have manually mapped some of the fields in the tables
I'm working with.

What I have is a table(A) that I need to join with 10 other
tables....I'm joining on an identifier in the (A) that may exist many
times in any of the other 10 tables...and may not be in ANY of the
tables.

When I run this query:

SELECT
SAMPLES.PK_SampleUID,UDFSAMPLEDATA02.AlphaData,UDF SAMPLEDATA01.AlphaData,UDFSAMPLEDATA03.AlphaData,
UDFSAMPLEDATA05.AlphaData, UDFSAMPLEDATA06.AlphaData,
UDFSAMPLEDATA07.AlphaData, UDFSAMPLEDATA08.AlphaData,
UDFSAMPLEDATA09.AlphaData,UDFSAMPLEDATA10.AlphaDat a

FROM SAMPLES, UDFSAMPLEDATA01
,UDFSAMPLEDATA02,UDFSAMPLEDATA03,UDFSAMPLEDATA05,U DFSAMPLEDATA06
,UDFSAMPLEDATA07 ,
UDFSAMPLEDATA08, UDFSAMPLEDATA09, UDFSAMPLEDATA10

WHERE UDFSAMPLEDATA02.AlphaData<>' ' AND
UDFSAMPLEDATA01.FK_SampleUID=SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA02.FK_SampleUID=SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA03.FK_SampleUID= SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA05.FK_SampleUID = SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA06.FK_SampleUID = SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA07.FK_SampleUID = SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA08.FK_SampleUID = SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA09.FK_SampleUID=SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA10.FK_SampleUID = SAMPLES.PK_SampleUID

I return what appears to be the gazillion COMBINATIONS of all the
fields in all the tables...they query doesn't even finish before the
ODBC driver I'm working with crashes my VBscript...

Is there some way to take the multiple returned rows from a join and
work them all into ONE row per identifier?
Any help I can garner would just make my week!

TIA!

JA database that doesn't support the ANSI92 syntax may have its own
proprietary syntax for outer joins. Otherwise, you can use UNION in
place of an outer join, assuming UNION and EXISTS are supported:

CREATE TABLE T1 (x INTEGER PRIMARY KEY)
CREATE TABLE T2 (x INTEGER PRIMARY KEY)

INSERT INTO T1 (x) VALUES (1)
INSERT INTO T1 (x) VALUES (2)
INSERT INTO T2 (x) VALUES (1)

SELECT T1.x, T2.x
FROM T1, T2
WHERE T1.x = T2.x
UNION ALL
SELECT T1.x, NULL
FROM T1
WHERE NOT EXISTS
(SELECT *
FROM T2
WHERE T2.x = T1.x) ;

--
David Portas
SQL Server MVP
--|||Thanks for the direction David - I'll start working with this...I'm
sure I'll have more questions as I go.

j

David Portas wrote:
> A database that doesn't support the ANSI92 syntax may have its own
> proprietary syntax for outer joins. Otherwise, you can use UNION in
> place of an outer join, assuming UNION and EXISTS are supported:
> CREATE TABLE T1 (x INTEGER PRIMARY KEY)
> CREATE TABLE T2 (x INTEGER PRIMARY KEY)
> INSERT INTO T1 (x) VALUES (1)
> INSERT INTO T1 (x) VALUES (2)
> INSERT INTO T2 (x) VALUES (1)
> SELECT T1.x, T2.x
> FROM T1, T2
> WHERE T1.x = T2.x
> UNION ALL
> SELECT T1.x, NULL
> FROM T1
> WHERE NOT EXISTS
> (SELECT *
> FROM T2
> WHERE T2.x = T1.x) ;
> --
> David Portas
> SQL Server MVP
> --|||You're welcome, but you'll probably find better help in a group or
forum dedicated to the database you are using. This is a Microsoft SQL
Server group.

--
David Portas
SQL Server MVP
--