Hi everyone,
I have an acquaintance who is doing some A2003 development who was
asking a question on another forum (not a technical one) about an SQL
problem he's having.
As far as I can tell, he *is* using Jet as the database engine, yet he
says a statement like the following is working:
SELECT orders.ordersid ,
recipies.recipi estitle,
orderscontent.o cquantity,order scontent.ocissp lit
FROM Orders, OrdersContent, Recipies
WHERE Orders.Orders_S essionsID = MMColParam
AND Orders.OrdersID = OrdersContent.O C_OrdersID
AND OrdersContent.O C_MC_RecipiesID = Recipies.Recipi esID
How is this?
The join structure, specified in the where clause, is what one would
normally do in Oracle and not Jet. Not even MS SQL as far as I know.
Any comments? I'd really like to help steer him correctly as I fear
he's going to make a hash of his project using SQL like the above, but
am I wrong? Can a structure like this work in Access/Jet?
Thanks very much.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me 19  1679 
Tim Marshall wrote:
The join structure, specified in the where clause, is what one would
normally do in Oracle and not Jet. Not even MS SQL as far as I know.
Well, I'll be hornswaggled. I just tried, from one of my jet applications:
SELECT ord_act_abbrev, unt_name, unt_code
FROM tbl_orders, tbl_units
WHERE ord_unt_fk = unt_pk
The above is an "Oracle" type join. The following is how the Access
query builder constructs the SQL:
SELECT tbl_orders.ORD_ ACT_ABBREV, tbl_units.UNT_N AME, tbl_units.UNT_C ODE
FROM tbl_orders INNER JOIN tbl_units ON tbl_orders.ORD_ UNT_FK =
tbl_units.UNT_P K
Both give the same results!!!!!!
What is going on?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
>What is going on?
Are you asking if some database engines are nimble enough to recognize
that...
SELECT foo.bar, baz.thing
FROM foo, baz
WHERE foo.key=baz.key
....is functionally equivalent to...
SELECT foo.bar, baz.thing
FROM foo INNER JOIN baz
ON foo.key=baz.key
....?
In my experience, some of them are. If Jet is among them, it should be
of no surprise.
Gord
Tim Marshall wrote:
Tim Marshall wrote:
The join structure, specified in the where clause, is what one would
normally do in Oracle and not Jet. Not even MS SQL as far as I know.
Well, I'll be hornswaggled. I just tried, from one of my jet applications:
SELECT ord_act_abbrev, unt_name, unt_code
FROM tbl_orders, tbl_units
WHERE ord_unt_fk = unt_pk
The above is an "Oracle" type join. The following is how the Access
query builder constructs the SQL:
SELECT tbl_orders.ORD_ ACT_ABBREV, tbl_units.UNT_N AME, tbl_units.UNT_C ODE
FROM tbl_orders INNER JOIN tbl_units ON tbl_orders.ORD_ UNT_FK =
tbl_units.UNT_P K
Both give the same results!!!!!!
What is going on?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
On Thu, 02 Nov 2006 22:04:23 -0330, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:
This seems legal syntax in all 3 environments: a carthesian product
with a where clause.
-Tom.
>Tim Marshall wrote:
>The join structure, specified in the where clause, is what one would
normally do in Oracle and not Jet. Not even MS SQL as far as I know.
Well, I'll be hornswaggled. I just tried, from one of my jet applications:
SELECT ord_act_abbrev, unt_name, unt_code
FROM tbl_orders, tbl_units
WHERE ord_unt_fk = unt_pk
The above is an "Oracle" type join. The following is how the Access
query builder constructs the SQL:
SELECT tbl_orders.ORD_ ACT_ABBREV, tbl_units.UNT_N AME, tbl_units.UNT_C ODE
FROM tbl_orders INNER JOIN tbl_units ON tbl_orders.ORD_ UNT_FK =
tbl_units.UNT_ PK
Both give the same results!!!!!!
What is going on?
Tim Marshall <TI****@PurpleP andaChasers.Moe rtheriumwrote in
news:ei******** **@coranto.ucs. mun.ca:
Tim Marshall wrote:
>The join structure, specified in the where clause, is what
one would normally do in Oracle and not Jet. Not even MS SQL
as far as I know.
Well, I'll be hornswaggled. I just tried, from one of my jet
applications:
SELECT ord_act_abbrev, unt_name, unt_code
FROM tbl_orders, tbl_units
WHERE ord_unt_fk = unt_pk
The above is an "Oracle" type join. The following is how the
Access query builder constructs the SQL:
SELECT tbl_orders.ORD_ ACT_ABBREV, tbl_units.UNT_N AME,
tbl_units.UNT_C ODE FROM tbl_orders INNER JOIN tbl_units ON
tbl_orders.ORD_ UNT_FK = tbl_units.UNT_P K
Both give the same results!!!!!!
What is going on?
Good old-fashioned SQL is what's going on. prior to ANSI SQL-92
Access has always supported the old style, and it's the only way
to get the query builder to accept a non-equi join or a join on
a calculated expression. .
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
Tom van Stiphout wrote:
On Thu, 02 Nov 2006 22:04:23 -0330, Tim Marshall
This seems legal syntax in all 3 environments: a carthesian product
with a where clause.
Ah, I dind't see that... I guess that's why it's working in Jet.
BTW, this doesn't create a cartesian join in Oracle; it's the legitimate
way to create a join versus the method used in the from clause in Jet.
For the equivalent of left/right joins in Jet, Oracle uses a (+) on one
side of the where clause join and this throws an error in Jet.
Back to Jet, on first thought, a cartesian join is not very efficient,
even if there is a where clause. I can't really test this as all of my
data is Oracle - my Jet data at home are very, small, <1000 records...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Bob Quintal wrote:
Good old-fashioned SQL is what's going on. prior to ANSI SQL-92
Access has always supported the old style,
Hi Bob,
I thought Access only came out in 1992.
The "old style" is what Oracle uses.
Access does not accept the old style (referred to as theta join
methodology) becaused the method of creating outer joins (ie, what Jet
calls left/right joins), does not work with Jet SQL.
As far as I can tell, Tom seems to be right about cartesian joins.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
On Thu, 02 Nov 2006 23:56:51 -0330, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:
The efficiency is maintained because the Access query optimizer
"converts" the sql to one using joins.
-Tom.
>Tom van Stiphout wrote:
>On Thu, 02 Nov 2006 22:04:23 -0330, Tim Marshall
>This seems legal syntax in all 3 environments: a carthesian product
with a where clause.
Ah, I dind't see that... I guess that's why it's working in Jet.
BTW, this doesn't create a cartesian join in Oracle; it's the legitimate
way to create a join versus the method used in the from clause in Jet.
For the equivalent of left/right joins in Jet, Oracle uses a (+) on one
side of the where clause join and this throws an error in Jet.
Back to Jet, on first thought, a cartesian join is not very efficient,
even if there is a where clause. I can't really test this as all of my
data is Oracle - my Jet data at home are very, small, <1000 records...
Tom van Stiphout wrote:
The efficiency is maintained because the Access query optimizer
"converts" the sql to one using joins.
I see, that makes sense.
My thanks to you, Gord, and Bob, for taking the time to reply.
FWIW, this is a reference to the topic on the forum I'm referring to.
It's generally a hobby (broadly related to the toys from my web site
below) forum, but there are boards for such things as computers and web
design questions: http://theminiaturespage.com/boards/msg.mv?id=90319
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
On Nov 2, 11:17 pm, Tim Marshall
<TIM...@PurpleP andaChasers.Moe rtheriumwrote:
Bob Quintal wrote:
Good old-fashioned SQL is what's going on. prior to ANSI SQL-92
Access has always supported the old style,Hi Bob,
I thought Access only came out in 1992.
There is still the requirement to re-write the query parser, debug,
beta test, etc before it gets to user desktops.
The "old style" is what Oracle uses.
So does Access. It also handles the new style.
Access does not accept the old style (referred to as theta join
methodology) becaused the method of creating outer joins (ie, what Jet
calls left/right joins), does not work with Jet SQL.
That is total nonsense. Access's query parser is incomplete in that it
fails to resolve a FULL outer join, but it does support theta joins.
As far as I can tell, Tom seems to be right about cartesian joins.
well it's not that cut and dried. the query parser may and in (Access,
does) evaluate the filters before getting rows, using indexes when
available, even creating hidden ones sometimes. So Access doesn't
Generate a cartesian product and then filter.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Steve Lefevre |
last post by:
Hey folks -
I'm working on an access database that I'm hoping to be able to sell to
small/medium sized business. I would like to do some trial and demoing, but
I'm worried about client's ability to undo the security measure that I put
in place. Specifically, I know that holding the 'shift' key disables all
startup options on launch, so that cancels any ability I have to shut down a
time-period demo.
How have you done it?
|
by: Omey Samaroo |
last post by:
I converted an access97 Database to Access 2K and have discovered that none
of the control buttons work.
Is there a simple work around ?
Thanks
Omey
|
by: Barry Young |
last post by:
I have Access 2000 installed on a machine and I create the MDE just fine.
When I copy the MDE file over to another machine that has Access 2000
installed, the references are hosed and I get unable to find function error.
There are no third party controls in the application.
This target machine is on a network requiring domain login, but the user is
set up as a Local Administrator.
Any ideas or code that I can use to work arount this...
|
by: RC |
last post by:
I just got my first Access project that I am getting paid for. I have
done other Access work for non-profits that I did not get paid for. I
definitely have the work but we did not settle the price yet. Can any
of you give me some advice on how much to charge? What factors
determine the price? The client is a small to medium size company in
my town in Wisconsin. The project is an inventory database to scan
bar codes of goods when they...
|
by: Paul T. RONG |
last post by:
Dear All,
I add two new tables to the database and then the disable shift key codes
don't work. These two new tables are actually created by two queries, and
only these two are in the front end, I didn't manage to move them to the
back end. I work with Access 2k under Win XP.
these are the codes that don't work (it worked for more than a year very
well):
| | |
by: google.1.jvmail |
last post by:
I have over 10 years strong experience coding in VBA, developing
complex multi-user applications in MS Access (all versions), have
worked with security, replication, and whole bunch of other aspects of
desktop development. I've even developed web apps interfacing with both
Access and SQL Server. I've done some with PHP, but my goal is to work
with MS products more now. The exception to that would be I like
working with Crystal reports.
...
|
by: scottyman |
last post by:
I can't make this script work properly. I've gone as far as I can with
it and the rest is out of my ability. I can do some html editing but
I'm lost in the Java world. The script at the bottom of the html page
controls the form fields that are required. It doesn't function like
it's supposed to and I can leave all the fields blank and it still
submits the form. Also I can't get it to transfer the file in the
upload section. The file name...
|
by: Jimmy |
last post by:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far:
<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")
|
by: Neil |
last post by:
I just noticed that control tips aren't working in any of my databases. I
recently installed Access 2003, and created a database in it, and noticed
that control tips weren't working in my controls. I was using control tips
with command buttons in the form header. But they also didn't work with the
command buttons in the detail section, nor with text boxes in the detail
section.
I tried converting the file back to Access 2000 format, but...
|
by: Lykins |
last post by:
We currently use Access 2003 in our company and have had this issues
from every version from Access 97 to 2003. We deal with large
databases and run a lot of queries over tables with millions of records
in them.
The problem comes in that when we pull a dataset out of a large table
we do not get the same result every time. Example is transaction count
for store 1 shows 3000 one time, the next run it is 3015, the next is
2089. All of...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
