Outer command with 3 tables

Steve · External Since: Feb 12, 2004 Posts: 16

Hi,

I have an Excel sheet which has a query to an Informix database stored on a
Unix server. The query works fine but I need to query 3 tables and without
the parameter OUTER I don't have the proper result. I read somewhere that in
Excel you can't do a Outer join with 3 tables. Is there a way to get this
work?

Here is the situation:
Table1 contains new product codes to add to the system.
Table2 linked to Table1, is the inventory table which contains product code
descriptions, product costs and.
Table3 linked to Table2, contains product prices and measuring unit.

So I have to create a list of new product codes which shows descriptions and
prices. By doing this, I don't have all record of table1 because of Table3
missing codes. New product codes that don't have prices yet are ignored by
the query. So I found the parameter OUTER which should fix this but in Excel
it appears that a 3 table query won't allow the use of it.

What would be solutions to this problem.

Any help will be greatly appreciated

Steve Amirault

Andy Wiggins · External Since: Jan 26, 2004 Posts: 18

Hi,

Below is a link to examples of getting data from Access databases using SQL.
The SQL query is generated in Excel's VBA, but is executed by the database.
Excel is simply a way of getting the message to the database and displaying
the resulting data that comes back.

If you can construct the SQL query on your database, then all you have to do
is transfer the SQL code into one of these examples.

These examples are for Excel and Access but should be fairly easy for you
change to Informix. A caveat from me - I don't know Informix, but if it's
ODBC compliant there shouldn't be a problem.

This also includes examples of using variables in SQL queries.
<a style='text-decoration: underline;' href="http://www.bygsoftware.com/examples/sql.html" target="_blank">http://www.bygsoftware.com/examples/sql.html</a>

Or you can get there from the "Excel with Access Databases" section on page:
<a style='text-decoration: underline;' href="http://www.bygsoftware.com/examples/examples.htm" target="_blank">http://www.bygsoftware.com/examples/examples.htm</a>

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.

--
Regards
Andy Wiggins
<a style='text-decoration: underline;' href="http://www.BygSoftware.com" target="_blank">www.BygSoftware.com</a>
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Steve" <as.DeleteThis@joe.ca> wrote in message
news:ejR43fM7DHA.1852@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> I have an Excel sheet which has a query to an Informix database stored on
a
> Unix server. The query works fine but I need to query 3 tables and without
> the parameter OUTER I don't have the proper result. I read somewhere that
in
> Excel you can't do a Outer join with 3 tables. Is there a way to get this
> work?
>
> Here is the situation:
> Table1 contains new product codes to add to the system.
> Table2 linked to Table1, is the inventory table which contains product
code
> descriptions, product costs and.
> Table3 linked to Table2, contains product prices and measuring unit.
>
> So I have to create a list of new product codes which shows descriptions
and
> prices. By doing this, I don't have all record of table1 because of
Table3
> missing codes. New product codes that don't have prices yet are ignored by
> the query. So I found the parameter OUTER which should fix this but in
Excel
> it appears that a 3 table query won't allow the use of it.
>
> What would be solutions to this problem.
>
> Any help will be greatly appreciated
>
> Steve Amirault
>
>

onedaywhen · External Since: Dec 03, 2003 Posts: 36

You haven't posted your table schema, sample data, SQL or code so
there is nothing to go.

But hang on, Excel isn't an DBMS so it can't do joins at all! Are you
talking about a data access technology (MS Query, ADO, etc) used
within Excel? These usually use 'ODBC pass through' i.e. the syntax is
passed to the source and it not verified by the client. So if you are
experiencing a limit to the number of outer joins I don't think it is
*Excel* which is imposing it.

You can query Excel workseets as ADO data sources using MS Jet. Using
Jet 4.0 I can successfully query three tables using two outer joins
e.g.

SELECT T3.RefID
FROM (PersonalDetails T1
Left JOIN PersonalDetails T2
ON T1.RefID=T2.RefID)
LEFT JOIN PersonalDetails T3
ON T2.RefID=T3.RefID

So I don't think Jet is imposing the limit.

I've used a DBMS (Intersystems Caché) which seemed to allow only one
outer join per query. The workaround was to use inner joins and only
one outer join. It seemed counterintuitive but did work.

Without anything to go on, I can't make any specific suggestions.

--

"Steve" <as.DeleteThis@joe.ca> wrote in message news:<ejR43fM7DHA.1852.DeleteThis@TK2MSFTNGP10.phx.gbl>...
> Hi,
>
> I have an Excel sheet which has a query to an Informix database stored on a
> Unix server. The query works fine but I need to query 3 tables and without
> the parameter OUTER I don't have the proper result. I read somewhere that in
> Excel you can't do a Outer join with 3 tables. Is there a way to get this
> work?
>
> Here is the situation:
> Table1 contains new product codes to add to the system.
> Table2 linked to Table1, is the inventory table which contains product code
> descriptions, product costs and.
> Table3 linked to Table2, contains product prices and measuring unit.
>
> So I have to create a list of new product codes which shows descriptions and
> prices. By doing this, I don't have all record of table1 because of Table3
> missing codes. New product codes that don't have prices yet are ignored by
> the query. So I found the parameter OUTER which should fix this but in Excel
> it appears that a 3 table query won't allow the use of it.
>
> What would be solutions to this problem.
>
> Any help will be greatly appreciated
>
> Steve Amirault

Steve · External Since: Feb 12, 2004 Posts: 16

Thanks for replying. I'll see what I can do with your suggestions though I
don't quite understand Andy's one.

Steve

"Steve" <as.TakeThisOut@joe.ca> a écrit dans le message de
news:ejR43fM7DHA.1852@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> I have an Excel sheet which has a query to an Informix database stored on
a
> Unix server. The query works fine but I need to query 3 tables and without
> the parameter OUTER I don't have the proper result. I read somewhere that
in
> Excel you can't do a Outer join with 3 tables. Is there a way to get this
> work?
>
> Here is the situation:
> Table1 contains new product codes to add to the system.
> Table2 linked to Table1, is the inventory table which contains product
code
> descriptions, product costs and.
> Table3 linked to Table2, contains product prices and measuring unit.
>
> So I have to create a list of new product codes which shows descriptions
and
> prices. By doing this, I don't have all record of table1 because of
Table3
> missing codes. New product codes that don't have prices yet are ignored by
> the query. So I found the parameter OUTER which should fix this but in
Excel
> it appears that a 3 table query won't allow the use of it.
>
> What would be solutions to this problem.
>
> Any help will be greatly appreciated
>
> Steve Amirault
>
>

Mikko Noromaa · External Since: Oct 30, 2003 Posts: 13

Hi,

> Thanks for replying. I'll see what I can do with your suggestions though I
> don't quite understand Andy's one.

To go by Andy's suggestion without doing any coding yourself, I suggest you
have a look at our product, ExcelSQL. It allows you to enter SQL queries as
cell comments, giving you the ability to control the exact SQL statement
sent to your database. It is easy to use OUTER joins as long as your
database supports them.

The only problem with ExcelSQL is that you will have to know SQL well enough
to write the SQL queries yourself.

For more information, see <a style='text-decoration: underline;' href="http://www.excelsql.com" target="_blank">http://www.excelsql.com</a>

--

Mikko Noromaa (mikkon@excelsql.com)
- SQL in Excel, check out ExcelSQL! - see <a style='text-decoration: underline;' href="http://www.excelsql.com" target="_blank">http://www.excelsql.com</a> -

Steve · External Since: Feb 12, 2004 Posts: 16

Still looking for a solution. I looked for OUTER statement, tried a lot of
things but can't get it. Here is the Query I need to run from excel to get
the result in excel. Can anybody help me on this please?

Thanks in advance.

SELECT pritel.pri_in1_code, pritel.pri_bar_code, pritel.pri_date,
prix1.pr1_unit_vente, prix1.pr1_fact_conv, pritel.pri_prix,
pritel.pri_type, pritel.pri_p_pers, pritel.pri_code,
pritel.pri_qte_main_tot, inv1.in1_desc_f, inv1.in1_cout,
prix1.pr1_prix_vente1
FROM prog.inv1 inv1, prog.pritel pritel, prog.prix1 prix1
WHERE pritel.pri_in1_code=inv1.in1_code AND prix1.pr1_in1_code =
inv1.in1_code AND pr1_date_deb = (select max (pr1_date_deb) from prix1
where prix1.pr1_in1_code=pritel.pri_in1_code)

Steve

"Steve" <as RemoveThis @joe.ca> a écrit dans le message de
news:ejR43fM7DHA.1852@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> I have an Excel sheet which has a query to an Informix database stored on
a
> Unix server. The query works fine but I need to query 3 tables and without
> the parameter OUTER I don't have the proper result. I read somewhere that
in
> Excel you can't do a Outer join with 3 tables. Is there a way to get this
> work?
>
> Here is the situation:
> Table1 contains new product codes to add to the system.
> Table2 linked to Table1, is the inventory table which contains product
code
> descriptions, product costs and.
> Table3 linked to Table2, contains product prices and measuring unit.
>
> So I have to create a list of new product codes which shows descriptions
and
> prices. By doing this, I don't have all record of table1 because of
Table3
> missing codes. New product codes that don't have prices yet are ignored by
> the query. So I found the parameter OUTER which should fix this but in
Excel
> it appears that a 3 table query won't allow the use of it.
>
> What would be solutions to this problem.
>
> Any help will be greatly appreciated
>
> Steve Amirault
>
>

Steve · External Since: Feb 12, 2004 Posts: 16

Hi,

Here is the working one:

SELECT pritel.pri_in1_code, pritel.pri_bar_code, pritel.pri_date,
pritel.pri_unit_vente,
prix1.pr1_fact_conv, pritel.pri_prix, pritel.pri_type, pritel.pri_p_pers,
pritel.pri_code, pritel.pri_qte_main_tot, inv1.in1_desc_f, inv1.in1_cout,
prix1.pr1_prix_vente1
FROM prog.pritel pritel, {oj prog.inv1 inv1 LEFT OUTER JOIN prog.prix1
prix1 ON
prix1.pr1_in1_code=inv1.in1_code} WHERE inv1.in1_code=pritel.pri_in1_code
AND
prix1.pr1_in1_code = inv1.in1_code AND pr1_date_deb = (select max
(pr1_date_deb) from prix1 )

Thanks for your support guys, I really appreciated it.

Steve

"Steve" <as.TakeThisOut@joe.ca> a écrit dans le message de
news:ejR43fM7DHA.1852@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> I have an Excel sheet which has a query to an Informix database stored on
a
> Unix server. The query works fine but I need to query 3 tables and without
> the parameter OUTER I don't have the proper result. I read somewhere that
in
> Excel you can't do a Outer join with 3 tables. Is there a way to get this
> work?
>
> Here is the situation:
> Table1 contains new product codes to add to the system.
> Table2 linked to Table1, is the inventory table which contains product
code
> descriptions, product costs and.
> Table3 linked to Table2, contains product prices and measuring unit.
>
> So I have to create a list of new product codes which shows descriptions
and
> prices. By doing this, I don't have all record of table1 because of
Table3
> missing codes. New product codes that don't have prices yet are ignored by
> the query. So I found the parameter OUTER which should fix this but in
Excel
> it appears that a 3 table query won't allow the use of it.
>
> What would be solutions to this problem.
>
> Any help will be greatly appreciated
>
> Steve Amirault
>
>