Board index Clipper

Multiple LEFT OUTER JOIN Syntax?

Multiple LEFT OUTER JOIN Syntax?

Post by Lynn C. Ormon » Thu, 05 Apr 2001 08:47:32



Hello Everyone,

I am creating an ODBC query to an Access database where the first table
(Listings)
has records for every Listings.ID, but each of the 2 tables I need to join
to it (Addendums and ListingMisc ) may or may not have records with a
matching value in their corresponding field.  The following statements work
as expected for one or the other join:

cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
Addendums.Addendum FROM
Listings LEFT OUTER JOIN Addendums ON (Listings.ID = Addendums.ListingID)

or

cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
ListingMisc.ListingFeatures1 FROM Listings LEFT OUTER JOIN ListingsMisc ON
(Listings.ID = ListingMisc.ListingID)

oServer1 := SQLSelect{ cSelect, oConn1 }
oServer1:Execute( cSelect )

The above Execute works correctly for either of the cSelect values listed.

How do I combine the Joins?  The MS ODBC help file says that both lists and
nested outer joins are supported, but I can't find samples of them and all
my attempts have returned syntax errors either at the FROM level or the JOIN
level.

Help???

Lynn C. Ormond
Lucero Research

 
 
 
Top

Multiple LEFT OUTER JOIN Syntax?

Post by Mark Keog » Thu, 05 Apr 2001 10:28:46


I don't know how Access SQL works, but the ANSI way is to union the two
results sets together, ie :-

SELECT Listings.ID, Listings.Class, Listings.Type,
Addendums.Addendum FROM
Listings LEFT OUTER JOIN Addendums ON (Listings.ID = Addendums.ListingID)

UNION

SELECT Listings.ID, Listings.Class, Listings.Type,
ListingMisc.ListingFeatures1 FROM Listings LEFT OUTER JOIN ListingsMisc ON
(Listings.ID = ListingMisc.ListingID)

This will return the two results sets combined into one. As long as the
number of columns, and their types match, on both sides of the union, you
should be right.

Or

SELECT Listings.ID, Listings.Class, Listings.Type,
                 Addendums.Addendum, ListingMisc.ListingFeatures1
FROM      Listings, Addendums, ListingsMisc

WHERE   Listings.ID (+)   = Addendums.ListingID
AND         Listings.ID (+)   = ListingMisc.ListingID

But the UNION is the best bet.

HTH

Mark


>Hello Everyone,

>I am creating an ODBC query to an Access database where the first table
>(Listings)
>has records for every Listings.ID, but each of the 2 tables I need to join
>to it (Addendums and ListingMisc ) may or may not have records with a
>matching value in their corresponding field.  The following statements work
>as expected for one or the other join:

>cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
>Addendums.Addendum FROM
>Listings LEFT OUTER JOIN Addendums ON (Listings.ID = Addendums.ListingID)

>or

>cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
>ListingMisc.ListingFeatures1 FROM Listings LEFT OUTER JOIN ListingsMisc ON
>(Listings.ID = ListingMisc.ListingID)

>oServer1 := SQLSelect{ cSelect, oConn1 }
>oServer1:Execute( cSelect )

>The above Execute works correctly for either of the cSelect values listed.

>How do I combine the Joins?  The MS ODBC help file says that both lists and
>nested outer joins are supported, but I can't find samples of them and all
>my attempts have returned syntax errors either at the FROM level or the
JOIN
>level.

>Help???

>Lynn C. Ormond
>Lucero Research



 
 
 
Top

Multiple LEFT OUTER JOIN Syntax?

Post by Michael Haensc » Thu, 05 Apr 2001 15:10:19


Try

 cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
Addendums.Addendum, ListingMisc.ListingFeatures1 FROM
 (Listings LEFT OUTER JOIN Addendums ON (Listings.ID = Addendums.ListingID))
LEFT OUTER JOIN ListingsMisc ON (Listings.ID = ListingMisc.ListingID)

Michael

 
 
 
Top

Multiple LEFT OUTER JOIN Syntax?

Post by Lynn C. Ormon » Thu, 05 Apr 2001 23:33:02


Michael,

It worked beautifully!  Thank YOU!
(Of course, I did have to correct all of my typos first...)

I'm actually selecting 240 fields from 4 files now and your syntax worked.
Thanks again!

--

Lynn C. Ormond
Lucero Research


Quote:

> Try

>  cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
> Addendums.Addendum, ListingMisc.ListingFeatures1 FROM
>  (Listings LEFT OUTER JOIN Addendums ON (Listings.ID =

Addendums.ListingID))
Quote:> LEFT OUTER JOIN ListingsMisc ON (Listings.ID = ListingMisc.ListingID)

> Michael

 
 
 
Top

Multiple LEFT OUTER JOIN Syntax?

Post by Lynn C. Ormon » Thu, 05 Apr 2001 23:37:25


Thank you, Mark.

Lynn C. Ormond
Lucero Research


> I don't know how Access SQL works, but the ANSI way is to union the two
> results sets together, ie :-

> SELECT Listings.ID, Listings.Class, Listings.Type,
> Addendums.Addendum FROM
> Listings LEFT OUTER JOIN Addendums ON (Listings.ID = Addendums.ListingID)

> UNION

> SELECT Listings.ID, Listings.Class, Listings.Type,
> ListingMisc.ListingFeatures1 FROM Listings LEFT OUTER JOIN ListingsMisc ON
> (Listings.ID = ListingMisc.ListingID)

> This will return the two results sets combined into one. As long as the
> number of columns, and their types match, on both sides of the union, you
> should be right.

> Or

> SELECT Listings.ID, Listings.Class, Listings.Type,
>                  Addendums.Addendum, ListingMisc.ListingFeatures1
> FROM      Listings, Addendums, ListingsMisc

> WHERE   Listings.ID (+)   = Addendums.ListingID
> AND         Listings.ID (+)   = ListingMisc.ListingID

> But the UNION is the best bet.

> HTH

> Mark


> >Hello Everyone,

> >I am creating an ODBC query to an Access database where the first table
> >(Listings)
> >has records for every Listings.ID, but each of the 2 tables I need to
join
> >to it (Addendums and ListingMisc ) may or may not have records with a
> >matching value in their corresponding field.  The following statements
work
> >as expected for one or the other join:

> >cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
> >Addendums.Addendum FROM
> >Listings LEFT OUTER JOIN Addendums ON (Listings.ID = Addendums.ListingID)

> >or

> >cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
> >ListingMisc.ListingFeatures1 FROM Listings LEFT OUTER JOIN ListingsMisc
ON
> >(Listings.ID = ListingMisc.ListingID)

> >oServer1 := SQLSelect{ cSelect, oConn1 }
> >oServer1:Execute( cSelect )

> >The above Execute works correctly for either of the cSelect values
listed.

> >How do I combine the Joins?  The MS ODBC help file says that both lists
and
> >nested outer joins are supported, but I can't find samples of them and
all
> >my attempts have returned syntax errors either at the FROM level or the
> JOIN
> >level.

> >Help???

> >Lynn C. Ormond
> >Lucero Research


 
 
 
Top

Multiple LEFT OUTER JOIN Syntax?

Post by Thomas Olszewick » Fri, 06 Apr 2001 00:25:49


Mark,
Sorry Mark,  Your UNION solution will return matching rows twice.
Your second solution is the way to go.
Thomas Olszewicki
CPAS Systems Inc.


> I don't know how Access SQL works, but the ANSI way is to union the two
> results sets together, ie :-

> SELECT Listings.ID, Listings.Class, Listings.Type,
> Addendums.Addendum FROM
> Listings LEFT OUTER JOIN Addendums ON (Listings.ID = Addendums.ListingID)

> UNION

> SELECT Listings.ID, Listings.Class, Listings.Type,
> ListingMisc.ListingFeatures1 FROM Listings LEFT OUTER JOIN ListingsMisc ON
> (Listings.ID = ListingMisc.ListingID)

> This will return the two results sets combined into one. As long as the
> number of columns, and their types match, on both sides of the union, you
> should be right.

> Or

> SELECT Listings.ID, Listings.Class, Listings.Type,
>                  Addendums.Addendum, ListingMisc.ListingFeatures1
> FROM      Listings, Addendums, ListingsMisc

> WHERE   Listings.ID (+)   = Addendums.ListingID
> AND         Listings.ID (+)   = ListingMisc.ListingID

> But the UNION is the best bet.

> HTH

> Mark


> >Hello Everyone,

> >I am creating an ODBC query to an Access database where the first table
> >(Listings)
> >has records for every Listings.ID, but each of the 2 tables I need to
join
> >to it (Addendums and ListingMisc ) may or may not have records with a
> >matching value in their corresponding field.  The following statements
work
> >as expected for one or the other join:

> >cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
> >Addendums.Addendum FROM
> >Listings LEFT OUTER JOIN Addendums ON (Listings.ID = Addendums.ListingID)

> >or

> >cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
> >ListingMisc.ListingFeatures1 FROM Listings LEFT OUTER JOIN ListingsMisc
ON
> >(Listings.ID = ListingMisc.ListingID)

> >oServer1 := SQLSelect{ cSelect, oConn1 }
> >oServer1:Execute( cSelect )

> >The above Execute works correctly for either of the cSelect values
listed.

> >How do I combine the Joins?  The MS ODBC help file says that both lists
and
> >nested outer joins are supported, but I can't find samples of them and
all
> >my attempts have returned syntax errors either at the FROM level or the
> JOIN
> >level.

> >Help???

> >Lynn C. Ormond
> >Lucero Research


 
 
 
Top

1. Left Outer Join Syntax

What is the correct syntax for the following.
I have table A joined to table B and C. I also have table A left outer
joined to tables D and E
and in turn D is joined to F

SELECT A.*,B.*,C.*,D.*,E.*,F.*
FROM A
          LEFT OUTER JOIN D
          ON A.colD      = D.colA
          LEFT OUTER JOIN E
           ON A.colE      = E.colA,
         B,
         C
WHERE A.col1 = ?
AND     A.colB = B.colA
AND     A.colC = C.colA

(I know I can move the B and C table joins and make them explicit joins
off A)

How do I include the join of table F to table D where F.colD = D.colF in
the case where 1) F is a LEFT OUTER and 2) where F is plain (INNER?)
join  

Thanks in advance

Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

2. Web server and smtp/pop server

3. Left outer join on multiple tables

4. wavelet hardware ?

5. What is Syntax for multiple FULL OUTER JOINS?

6. Timestamp service

7. LEFT OUTER JOIN how to use?

8. LEFT OUTER JOIN & inheritance

9. LEFT OUTER JOIN and Update()

10. Left Outer Join of Views?

11. LEFT OUTER JOIN problem

12. SELECT with LEFT OUTER JOIN ON


All times are UTC
Board index