ANSI-89 DB JOINS

[jakelake]

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_Samp leUID,UDFSAMPLE DATA02.AlphaDat a,UDFSAMPLEDATA 01.AlphaData,UD FSAMPLEDATA03.A lphaData,
UDFSAMPLEDATA05 .AlphaData, UDFSAMPLEDATA06 .AlphaData,
UDFSAMPLEDATA07 .AlphaData, UDFSAMPLEDATA08 .AlphaData,
UDFSAMPLEDATA09 .AlphaData,UDFS AMPLEDATA10.Alp haData

FROM SAMPLES, UDFSAMPLEDATA01
,UDFSAMPLEDATA0 2,UDFSAMPLEDATA 03,UDFSAMPLEDAT A05,UDFSAMPLEDA TA06
,UDFSAMPLEDATA0 7 ,
UDFSAMPLEDATA08 , UDFSAMPLEDATA09 , UDFSAMPLEDATA10

WHERE UDFSAMPLEDATA02 .AlphaData<>' ' AND
UDFSAMPLEDATA01 .FK_SampleUID=S AMPLES.PK_Sampl eUID AND
UDFSAMPLEDATA02 .FK_SampleUID=S AMPLES.PK_Sampl eUID AND
UDFSAMPLEDATA03 .FK_SampleUID= SAMPLES.PK_Samp leUID AND
UDFSAMPLEDATA05 .FK_SampleUID = SAMPLES.PK_Samp leUID AND
UDFSAMPLEDATA06 .FK_SampleUID = SAMPLES.PK_Samp leUID AND
UDFSAMPLEDATA07 .FK_SampleUID = SAMPLES.PK_Samp leUID AND
UDFSAMPLEDATA08 .FK_SampleUID = SAMPLES.PK_Samp leUID AND
UDFSAMPLEDATA09 .FK_SampleUID=S AMPLES.PK_Sampl eUID AND
UDFSAMPLEDATA10 .FK_SampleUID = SAMPLES.PK_Samp leUID
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!

J

[David Portas]

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
--

[jakelake]

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
--

[David Portas]

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
--