Microsoft JET Database Engine error '80040e14' Syntax error in JOIN operation.

Hello carrzkiss

Your list of tables containsthe same table twice. This means you either want to have two criteria to the same table, or need two instances of the  same table, in which case at least one fo them needs to aliased:

    FROM ((
        ContentTypes
        RIGHT JOIN Sierra ON ContentTypes.contenttypeid = Sierra.contenttypeid)
        LEFT JOIN Cats
            ON Sierra.catid = Cats.catid
            AND BrushStrokes.catid = Cats.catid)

    FROM (((
        ContentTypes
        RIGHT JOIN Sierra ON ContentTypes.contenttypeid = Sierra.contenttypeid)
        LEFT JOIN Cats C1 ON Sierra.catid = C1.catid)
        LEFT JOIN Cats C2 ON BrushStrokes.catid = C2.catid)

In the second example (more likely), you would then have the fields C1.CatTypeID, C1.CatDescription, C2.CatTypeID, and C2.CatDescription

Cheers!
(°v°)

Hello Harfang;

I tried both of the examples you provided, and I still receive the same [Syntax Error in JOIN operation]

On the 2nd one, what is the [C1, C2]
What do they stand for? And do I have to add something else in to identify these "2"?
Or are they simply just pointers For Cats?

Either way, I tried them both and still no difference?

I have tried to do the [Query] [Wizard] & [Design View] in Access, but am a little confused on how to actually do it.
I would LOVE to learn how it is done.
I am reading through [Active Server Pages Bible] About how they created the [Query] in Access.
And have tried to implement the same thing with my Table(s) but have not succedded on it.

Please by all means, if you have any information about doing the [Query Wizard] in Access
(or) The [Query - Design View] (Which the Design View is what they show in the book I have)
I would love to read it (or) See & Read it. How ever the case may be.

I'll get back to this later.
(°v°)

I think that we might be getting somewhere.
The code you suggested:

 FROM (((
        ContentTypes
        RIGHT JOIN Sierra ON ContentTypes.contenttypeid = Sierra.contenttypeid)
        LEFT JOIN Cats ON Sierra.catid = Cats.catid)
        LEFT JOIN BrushStrokes ON Cats.catid = BrushStrokes.catid)

I corrected the lines in my code, and compiled and run (Compiled = Saved)
The Error went away, then I got another error:

Microsoft JET Database Engine error '80004005' The specified field 'Display' could refer to more than one table listed in the From clause of your sql statement.

So I changed it to do this:

Sierra.Display=1 AND BrushStrokes.Display=1 AND

And it ran without Error.
I just have to complete the testing to make sure that everything runs properly.
But I think I understand more so now, where the problem was at.

Here is the entire new line of code:

FROM (((ContentTypes RIGHT JOIN Sierra ON ContentTypes.contenttypeid = Sierra.contenttypeid)" & _
      "LEFT JOIN Cats ON Sierra.catid = Cats.catid) LEFT JOIN BrushStrokes ON Cats.catid = BrushStrokes.catid) WHERE Sierra.Display=1 AND BrushStrokes.Display=1 AND (Sierra.ContentId LIKE '%" & keywords & "%' OR Sierra.Product_Title LIKE '%" & keywords & "%' OR Sierra.Product_Description LIKE '%" & keywords & "%') ORDER BY Product_Title"

Harfang;

  If you ever get the opertunity to get back with me on the Access Queries.
That would be great.
Please let me know?

Wayne

Hello carrzkiss

Ok, a quick run-down on the access query builder... Open your database (or rather a copy of your database, you don't need your live production database for this), find the query tab and click [New].

The "add table" dialog will open automatically, but you can also get it later by choosing the corresponding button or menu command, so no hurry there. Start by [Add]ing one or a few tables. You can remove tables with [Del] or with the context menu.

The upper pane shows the tables you have selected. This is in fact the FROM clause of your query. To link tables, drag one field of a table  to a field on another table. This will create the links. To delete a link, select it and press [Del]. To choose between INNER, LEFT, and RIGHT JOINs, double click a link to get a dialog box.

The grid in the lower pane allows you to do the rest. The first row is your SELECT clause. You can put field names there (or double-click any field in a table of the upper pane), or type expressions. You can rename fields or expressions by placing the name at the left, followed by a colon. The second row shows or selects the table name, if needed.

(As you discovered with the field "Display", you need to specify the table name whenever you have two fields with the same name in different tables, which is quite understandable.)

The next row, "Sort:", is the ORDER BY clause. It is evaluated from left to right. If you want a different column order than the sort order, you will have to repeat the columns, making  use of the next row, "Show:", to avoid duplicate columns in the output.

Finally, the rows below will create the WHERE clause. Criteria on the same row are linked by AND, wile the different rows (if used) are linked by OR.

Whenever your query is in a state where it works, you can save it, naturally, or switch to SQL view to study or change the actual SQL. Once you have a working query, switch to SQL view, copy the entire sentence and paste it to ASP, and add the needed quotes to turn it into a string expression.

Is that what you needed?

(°v°)

Forgot to say: I'm glad you have it working now, and thanks for points and grade!
(°v°)

Thank you for the very detailed information on the Access Query Builder.
That I will have to do sometime later on today, as I am half asleep.

I made a backup of what I have, and replaced it with the older copy, so that the site could go LIVE again.
With the original Table Structure, as I have a lot more bugs to work out. That I do not quite understand
What (or) where the issue is at, considering, it is not giving me any error's it is just simply showing
The page, the Menu, the Search Area, and the Colored HTML Table.
But no Search Data is appearing?
This is all new area's for me, working with this many different Tables, so I am very lost at the moment.

Though the Search Query (that you assisted me with here) is no longer given me any errors.
I am not sure if the rest of the code is properly done, even though it is not giving me any errors either.
Just a not-completed-page Displayed.

If you would like to have a look at what I have attempted to do, this is the EE link
https://www.experts-exchange.com/questions/21879395/Display-information-from-2-Tables.html

But it is only showing the "Output" HTML/ASP code,
Is you look at the beginning of the code, you will see what I have Commented where it stops
Reading from the code.

Maybe, when I get going in the morning, after some sleep.
I will be able to better look at what I have, and maybe you might have some suggestions with the other
EE Post mentioned above.

Until then, I am back off to get some sleep.

Thank you for your much needed valuable information.
It is going to come in very handy, once I learn exactly how it is all put together.

Wayne

Harfang;

 I was able to take SQL Query taken from the ASP page,
<%
response.write "SQL Statement: " & view_Sierra_sql
%>
And run it against my Database, Of which it did not display nothing,
(Which is running a {Search} on the site, so I am taking it that is the reason
Why it did not show nothing in the [Datasheet View]?)

But I was able to look at it against the [Design View]
And have only now, seen how the page Table's are brought together.

I was going to ask you how to create the JOIN's from: |Right | Left | Inner | Outter|
But, viewing this, I see now how it is done, which is.
(If I am understanding it correctly)
Would be something like this:

===================================
Table_1                   Table_2              Table_3
Table_1 RIGHT JOIN Table_2
LEFT JOIN Table_3 ON Table_2.field_name
===================================
If this is correct, then if the Table that is on the LEFT JOIN's the Table in the Center
Then it would be a RIGHT JOIN?
And then the Table on the RIGHT JOIN's the Table in the Center,
Then it would be a LEFT JOIN.

So, it would all go about how the Table are laid out in the Query Design View window
Will determin which Direction that they would JOIN?
Am I correct with this assumption?
-----------
Also, Does it really matter which way the JOIN if done in SQL Queries in the ASP Page?
Or do they have to be done a certain way in order for the code to properly access the Table's
In the Database?
And if so, what is the meanings behind the JOIN Statements?
(What does it all mean?)

I know this is a lot to ask from you.
I am going to see what I can dig up on this subject as well.
One reason why I am asking this is not only for myself, but for other's as well.

Thank you for everything thus far, you have really helped to open my eyes in utilizing Access to it's fullest.
Wayne

Wayne,

It really is best to try out different combinations in the query assistant, and then study the resulting SQL. Back in the old days, you had to learn everything with books, pen, and paper, but now you can start with nice graphical tools and then only look at the raw stuff.

There is no difference between LEFT and RIGHT JOINs. They refer to the order of the tables in the SQL sentence, and are not related to such notions as "left on the screen" for example. If you see:

    Table1        Table2
    ----------       ----------
    FieldA  <----  FieldD
    FieldB          FieldE

This could be one of:

Select * from Table1 right join Table2 on Table1.FieldA=Table2.FieldD
Select * from Table2 left join Table1 on Table2.FieldD=Table1.FieldA

They are just two ways to write the same query, and mean that all records from Table2 will be shown (perhaps several times, though), but that only matching fields from Table1 will be.

Cheers!
(°v°)

OK. That sums it up for me.

I just found out something, (Could not sleep again this morning: 5:42am NC USA)

While working in the Query, I tried to do the multiple Tables.
And come across the following Error:   Error 3258

Which means that basically I cannot do what I wanted to do, the way I "Thought" I could do it :)
Did you get that?

Basically.
In order for me to be able to do this:

Table Names
ContentTypes --> Where at in the Database the information will be sent from : Stores
Cats --> Categories of the Stores
Sierra --> Content of Store Sierra
BrushStrokes --> Content of Store BS

I will have to make up 2-Queries.
And have the 2nd Query make it's Query to the 1st Query
(That sounds very confusing)

=========From the Help File on the Error I received=========
The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. (Error 3258)
You tried to execute an SQL statement that contains multiple joins; the results of the query can differ depending on the order in which the joins are performed. For example, this error can occur if you execute the following SQL statement:

SELECT * FROM Customers, Orders, [Order Details],
Customers LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID,
Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID;


Executing this statement produces an error because the order of the joins is ambiguous. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. The following queries illustrate how you might construct the preceding query so that the INNER JOIN operation is performed before the LEFT JOIN and RIGHT JOIN operation:

Query1

SELECT * FROM Orders, [Order Details],
Orders INNER JOIN [Order Details]
ON Orders. OrderID = [Order Details].OrderID;


Query2

SELECT * FROM Customers, Query1,
Customers LEFT JOIN Query1
ON Customers.CustomerID = Orders.CustomerID;
=============================================

So, this is going to be what I am about to "try" ??
I have already created both Queries, when you posted here.
So now I am going to see if it will work??

If you have any advice on this, I would love to read you great words.

p.s.
After this is all said and done.
I will open another Post here on EE.
So that you can copy and paste (ALL) Responces to the "How" to use Access SQL Queries.
So, this will not be a total waste of your time. (I hope that it is not, as you have taught me things that
I have been asking around for the longest time)

Wayne

I also read that if you set everything to: INNER JOIN.
That would get rid of the Error.
But you would not be able to view anything in the [Datasheet View]
So, I tried it, and it does get rid of the Error
(Making an INNER JOIN. That is to Double Click on the JOIN line, and choose [1] on all JOIN's correct?)
That is what I did, do not know if that is the best way, but I cannot find enough information
On the [separate query] Routine.

I will do some more checking around and see what I can come up with.
I am going to run this code and see how it enteracts with the site.

Wayne

Wayne,

I think I can see how your query cannot work. Sierra and BrushStrokes are two stores, containing records describing each. You probably do not want to join records from them on the same row, but rather one below the other. In that case, you are not looking for the right query type.

For example, if both tables contain the same fields, you might in fact be looking for this:

Select *, 'Sierra' As Store From Sierra
UNION ALL
Select *, 'BrushStrokes' From BrushStrokes

Perhaps the output of this query is even what your base table should be like?

(°v°)

Hello Harfang.

To get a better undrestanding of the Tables or both the Stores.
https://www.experts-exchange.com/questions/21882452/Error-3258-Query-in-Query-Help-please.html

The link provides a Combined JOIN of both the Stores.
As well as 2-seperate Stores JOIN's with the "Cat & ContentType" Tables as well.

But, I beleive that would give you a better understanding of what is going on.
----------
I had checked in on the UNION, and the Table's have to be some what similuar in FieldNames.
(For what I understand)
And in the case of the 2 Stores Tables, the only things that have any simularity will be:
-------------
CatID  --> Category ID For each indivisual Sub-Category (From Table: Cat)
&
Aff_Name --> Name of the Appropriate Store Owner
&
Display --> To Display=1 (or) To Not Display=0
&
ContentTypeID --> ContentType is the Store Dept of the Site
&
FKeywords --> All Tables share this for the particular Stores Generated [Meta-Keywords]
&
FMetaDesc --> All Stores share this for the paticular Stores Generated [Meta-Description]
&
Impressions --> The Page Count for the site.
------------
Other then the above, all the other FieldNames are different.
If this will indeed Qualify as a [UNION] then I will be looking at creating a UNION Query.

So please have a look at the above EE Link post, and let me know what you think?
Also, in the link above, there are only about 3- of the above Items that are similuar in the Queries.

Wayne

You can create a UNION query using only those fields that are compatible. They do not need to have the same names, nor even the same data type. They just need to make sense to you, and the number of columns must be identical.

(°v°)

The # of Columns: Meaning that both Tables must have the same amount of Columns (Fields)
In order to use UNION? Is that correct?
If so, then it would not work, atleast not between these "2" tables.
Now, with the other Table(s) (That are not yet created for the other Stores) They will have
The same amount of Columns.
But the Fields will not be in the same location, nor will the names be the same either.
The Sierra Table, has about 7-more Fields then the BS Table does.

You need to choose the same number of columns, that's all:

Select FruitName, FruitColor, FruitTaste From tblFruits
UNION ALL
Select AnimalName, AnimalFamily, Null From tblAnimals
UNION ALL
Select Top 1 'this', 'works', 'also' From tblAnyTable
UNION ALL
Select DISTINCT Null, Null, '(end)' From tblSample
ORDER BY 1,2

Each of these tables can have many other fields, that doesn't matter, neither does the field order in them.

Cheers!
(°v°)

Ohhh OK.
So it does not matter how many Fields each indivisual Table has, it is only in the Query.
The Query has to have the same amount of Fields specified in it?
OK, I read it wrong when I was researching it the other day.
That makes things a lot easier.

So would this be about right for what I would need to do:

========================================
Select CatID, ContentID, Product_Title From Siearr
UNION ALL
Select BContentID, BSProductName, BSDescription From BrushStrokes
Order By BSProductName, Product_Title
========================================

How about the [RIGHT & LEFT JOIN]'s ?
Would they also play a part in this as well?
Or would I just add in all the Tables in the Access Query, and then choose:
Query | SQL Specific | UNION
After I have all my Joins made?

Thank you a bunch, you have cleared up so much for me.

Wayne

In your query, you put CatID in first column, but later BContentID. It will be accepted, but probably wrong. Since both S and BS have a field CatID, they should be in the same column, no?

For the sort order, you can only use the field names from the very first query. The next queries will use the same names, whatever you do. You can also just use the column numbers, as I did in my sample.

Each individual query in a union query can use several tables, with left joins, right joins, etc. You would basically copy the queries you showed in your other question {http:Q_21882452.html}, includin the joins to ContentTypes and Cats.

Good luck!
(°v°)