473,327 Members | 2,074 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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!

J

Jul 23 '05 #1
3 1288
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
--

Jul 23 '05 #2
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
--


Jul 23 '05 #3
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
--

Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Eric Myers | last post by:
Hello folks: (This message is also posted on the help forum at the pexpect sourceforge page, but all indentation in the code got stripped away when I submitted the post.) For some time I've...
100
by: Roose | last post by:
Just to make a tangential point here, in case anyone new to C doesn't understand what all these flame wars are about. Shorthand title: "My boss would fire me if I wrote 100% ANSI C code" We...
4
by: Nick | last post by:
Hi, I am trying to output a string of chinese characters as a text file. When I open a file for writing from VB, the file is automatically set to UTF-8 encoding (can tell by opening the file...
83
by: sunny | last post by:
Hi All What is C99 Standard is all about. is it portable, i mean i saw -std=C99 option in GCC but there is no such thing in VC++.? which one is better ANSI C / C99? can i know the major...
7
by: Paul Connolly | last post by:
char *s = "Hello"; s = 'J'; puts(s); might print "Jello" in a pre-ANSI compiler - is the behaviour of this program undefined in any pre-ANSI compiler - or would it always have printed "Jello"...
10
by: Michael B. Trausch | last post by:
Alright... I am attempting to find a way to parse ANSI text from a telnet application. However, I am experiencing a bit of trouble. What I want to do is have all ANSI sequences _removed_ from...
127
by: bz800k | last post by:
Hi Does this code satisfy ANSI C syntax ? void function(void) { int a = 2; a = ({int c; c = a + 2;}); /* <<-- here !! */ printf("a=%d\n", a);
41
by: jaysome | last post by:
It's been almost eight years since ISO/IEC approved ISO/IEC 9899:1999. Does anyone know if ANSI has approved it? A Google search shows arguably confusing answers as to whether ANSI has...
8
AmberJain
by: AmberJain | last post by:
HELLO, Is it necessary for a C programmer to have an ANSI C standard or it's sufficient to own Kernigham and Rithie's The C programming language? I know that the ritchie's book is quite brief...
6
by: Peng Yu | last post by:
Hi, ANSI and GNU C are different in some delicate aspects (I'm not sure about C++). For example, M_PI is not in ANSI C but in GNU C. Of course, to make my program most portable, I should go...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.