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.recipiestitle,
orderscontent.ocquantity,orderscontent.ocissplit
FROM Orders, OrdersContent, Recipies
WHERE Orders.Orders_SessionsID = MMColParam
AND Orders.OrdersID = OrdersContent.OC_OrdersID
AND OrdersContent.OC_MC_RecipiesID = Recipies.RecipiesID
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  1635 
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_NAME, tbl_units.UNT_CODE
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 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_NAME, tbl_units.UNT_CODE
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 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****@PurplePandaChasers.Moertheriumwrote:
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_NAME, tbl_units.UNT_CODE
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****@PurplePandaChasers.Moertheriumwrote 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_NAME,
tbl_units.UNT_CODE 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?
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****@PurplePandaChasers.Moertheriumwrote:
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...@PurplePandaChasers.Moertheriumwrote:
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
Tim Marshall <TI****@PurplePandaChasers.Moertheriumwrote 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_NAME,
tbl_units.UNT_CODE 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?
If you turn on SHOWPLAN, you'll see that both are optimized exactly
the same way because from a SQL point of view, they are absolutely
equivalent. But that's only for INNER joins. It's much more
complicated to do a LEFT or RIGHT join with a WHERE clause.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Tom van Stiphout <no*************@cox.netwrote in
news:f5********************************@4ax.com:
This seems legal syntax in all 3 environments: a carthesian
product with a where clause.
But it's not optimized as a filtered Cartesian product. SHOWPLAN
reveals that it uses index joins to reduce the filter sets before
presenting the final results, and it's the exact same optimization
that you get with the equivalent JOIN statement.
I thought this wasn't news to anyone?
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Tom van Stiphout <no*************@cox.netwrote in
news:lb********************************@4ax.com:
The efficiency is maintained because the Access query optimizer
"converts" the sql to one using joins.
Not quite -- the query optimizer recognizes the two as equivalent
and optmizes both exactly the same.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Bob Quintal <rq******@sPAmpatico.cawrote in
news:Xn**********************@66.150.105.47:
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. .
Yes, but you can easily put non = operators in join statements in
SQL view. True, if you then go to design view in the QBE you can
lose your join, but I would never use WHERE statements in place of
joins, especially where the joins are between several tables and
there are quite a few criteria.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"rq******@sympatico.ca" <bo*********@gmail.comwrote in
news:11**********************@h48g2000cwc.googlegr oups.com:
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.
This is exactly correct. Turn on SHOWPLAN and you can see that Jet
optimizes eqvivalent JOIN and WHERE clauses identically.
I discovered this many, many years ago. I've posted on it in this
forum several times, I believe.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/ rq******@sympatico.ca wrote:
>>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.
It only partly supports theta joins. And then, it is unclear to me as
to whether the "Support" of theta equijoins is a deliberate inclusion in
how the query parser works or if it is the result of a Cartesian join.
I suspect the latter.
I'm not sure why it should be regarded as nonsense. The SQL standards
have been written and the ANSI join is supposed to be the standard with
the older type theta joins probably included (I am speculating) becuase
it's what Oracle does. I suspect (again, speculation) that if Oracle
wasn't around, the standard would be ANSI joins et c'est tout, finale! 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
David W. Fenton wrote:
"rq******@sympatico.ca" <bo*********@gmail.comwrote in
news:11**********************@h48g2000cwc.googlegr oups.com:
>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.
This is exactly correct. Turn on SHOWPLAN and you can see that Jet
optimizes eqvivalent JOIN and WHERE clauses identically.
I discovered this many, many years ago. I've posted on it in this
forum several times, I believe.
If I recall correctly SQL Server Books On Line (in older versions) used to
describe the theta joins as "the old way" and the join syntax as
"preferred". In the 2005 Books On Line it just referes to them as two
different ways to write the query and no longer seems to give the join
syntax preference.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Bob Quintal wrote:
>
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. .
You can use non-equi joins in Access, but not in the query builder. In
SQL view you can do 'From A INNER JOIN B On A.ID >= B.ID'.
--
Bri
Bri <no*@here.comwrote in
news:0hS2h.249062$5R2.44110@pd7urf3no:
>
Bob Quintal wrote:
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. .
You can use non-equi joins in Access, but not in the query
builder. In SQL view you can do 'From A INNER JOIN B On A.ID
= B.ID'.
--
Bri
Hi Bri, you are saying exactly what I said. in different
words.You can also do neat things like FROM a INNER JOIN b ON
left(a.field1,3) = mid(b.field7,3,3)
I just wish there was a better query builder available.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
Bob Quintal wrote:
Hi Bri, you are saying exactly what I said. in different
words.You can also do neat things like FROM a INNER JOIN b ON
left(a.field1,3) = mid(b.field7,3,3)
I just wish there was a better query builder available.
That's still ANSI, not theta syntax.
You're right that it has limitations, but the Access query builder is
still pretty awesome in my opinion. Of course, I can't use it for my
Oracle work (which is all Oraclese in pass through queries).
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - 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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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) *...
|
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....
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
