How to use several joins in one query?

  • Thread starter Thread starter MIKQ
  • Start date Start date
M

MIKQ

Hi, I'm trying to do a query but somehow can't manage that stupid
access syntax.
While trying I created a query which reproducible crashes access!

What am I doing wrong?:

SELECT p.Problem, p.Problem_lang, l.Loesung, l.Loesung_lang,
h.Hersteller
FROM ((Problem AS p inner join Prob_Loes AS pl on p.ID_Problem =
pl.ID_Problem)
inner join Loesung AS l on pl.ID_Loesung = l.ID_Loesung)
left join Hersteller AS h on p.ID_Problem = h.ID_Problem;
 
Your SQL looks OK. Do you perhaps already have a relationship (in the
relationships window) between tables Problem and Hersteller?

Sam
 
What am I doing wrong?
Why do you think you are doing something wrong? I built what I thought was
your table structure and the query runs ok.
Is this a total crash as in having to restart the program?

Post some sample data from your tables.
 
Oh dear, yes I do.
Isn't that the way you create a RDBMS?
For a n:n relation between two table (Problem and Loesung) I created an
intersection table (Prob_Loes)
The PK in Prob_Loes consists of the two PK of Problem and Loesung.
And I dragged the PK from Loesung (Field ID_Loesung) to the FK in
Prob_Loes (Field: ID_Loesung).
I did the same with Problem (Field ID_Problem).
To bad I can't post a screenshot here.


The Table "Hersteller" is connected in the same way. (Intersection
Table Prob_Hers).


So connecting these in Access AND doing a query connecting theses
tables is wrong?

Greetings
MIKQ
 
As Karl says, I don't think you're doing anything wrong per se, but the new
query might not like the new relationship you're giving it, because it
contradicts the established relationship.

What you might want to do is to copy the 4 tables, Problem, Prob_Loes,
Loesung, and Hersteller, and make sure there is no relationdhip in the
Relationships window for the new tables. If there is, delete it. Don't delete
the relationships for the original tables, though. Now, re-do the SQL
statement using the new tables and see if the query runs.

Hope this helps,

Sam
Is this a total crash as in having to restart the program?
YES

MIKQ


Why do you think you are doing something wrong? I built what I thought was
Click to expand...
[quoted text clipped - 17 lines]
Click to expand...
 
I deleted all relations in Access but that query still crashed access.


SELECT p.Problem, p.Problem_lang, l.Loesung, l.Loesung_lang,
h.Hersteller
FROM ((Problem AS p INNER JOIN Prob_Loes AS pl ON
p.ID_Problem=pl.ID_Problem) INNER JOIN Loesung AS l ON
pl.ID_Loesung=l.ID_Loesung) LEFT JOIN Hersteller AS h ON
h.ID_Problem=p.ID_Problem;



Then I changed my select to:

SELECT p.Problem, p.Problem_lang, l.Loesung, l.Loesung_lang,
h.Hersteller
FROM ((Problem AS p INNER JOIN Prob_Loes AS pl ON
p.ID_Problem=pl.ID_Problem) INNER JOIN Loesung AS l ON
pl.ID_Loesung=l.ID_Loesung) INNER JOIN Hersteller AS h ON
h.ID_Problem=p.ID_Problem;


That one works but returns no rows.
A "RIGHT JOIN" won't be recognized, the "LEFT JOIN" still let Access
2002 crash.


Do I have to have plenty of data in all tables.
I 'm using an intentionally a "LFET JOIN" because I expect that
some tables won't contain matching data.

Please help!
 
Try breaking it down and making subqueries in Access' query designer; you can
test each subquery seperately to make sure it's working. You may have to
tinker with it to get the correct levels of subqueries. Try the last join
first, as in:

SELECT p.FieldList, h.FieldList FROM Problem AS p LEFT JOIN Hersteller AS h
ON h.ID_Problem = p.ID_Problem;

To clarify things, you may want to add a WHERE clause, such as WHERE h.
ID_Problem Is Null (or Is Not Null, as the case may be.)

Of course, substitute the correct field names as needed. Now test the query
and see if it gives you the correct results. If it does, save the query -
you'll need to name it, such as qryProblemSub1.

Now make a new query using qryProblemSub1 as one 'table' , and do another
level, as in:

SELECT qryProblemSub1.* FROM qryProblemSub1 AS p INNER JOIN Prob_Loes AS pl
ON
p.ID_Problem=pl.ID_Problem;

Test the query, save it and name. Then do the last (highest) level to get the
fields you need.

If Access still crashes, I suspect there's something wrong with one or more
of the tables.

Hope this helps,

Sam
 
It sure sounds like the

Hersteller.Hersteller

field is the problem....

you get no records on inner join...
which says to me they are going
to be NULL on outer join...

is that field a Yes/No field
(where Nulls cannot exist)?

Allen Browne just added:

Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html

of course I could be wrong...
 
Dear Gary & OfficeDev18,


I figured that the table "Hersteller" is the Prob too.


As tables "Problem" and "Hersteller" are n:n connected there is
a intersection
table called "Prob_Hers". I followed OfficeDev18 suggestion and
broke my
statement into small pieces.

I tried to do a LEFT OUTER JOIN in a query that would show me all rows
from "Problem" (ignoring if there is a "Hersteller" connected
to it) but only the rows from "Hersteller" that are connected to a
"Problem".

The main idea is to show all rows from "Problem" and if there is
additional info in other tables as "Hersteller" ("Version"
etc.) this should be shown too. If there is no data in "Herstller"
("Version" etc.) everything from Problem should still show up!

To begin with I did this:

SELECT p.Problem, p.Problem_lang, h.Hersteller
FROM (Problem As p LEFT JOIN Prob_Hers AS ph ON
p.ID_Problem=ph.ID_Problem) INNER JOIN Hersteller AS h ON
h.ID_Hersteller=ph.ID_Hersteller;

And Access tells me "Verknüpfungsausdruck nicht unterstützt." In
English that should be something like: The connecting
expression/operator is unsupported/wrong.

Am I doing a stupid mistake, or even some serious design error? My main
idea can't be so hard, can it?
 
I googled an found the error message in English:
It says: Error 3296 "Join expression not supported (4)"

Hope that makes it easier to help me....

MIKQ
 
you just cannot nest inner joins
within an outer join....

save the inner join part as a separate
query, then left join to that query....

that way Access won't get confused...


:
Dear Gary & OfficeDev18,


I figured that the table "Hersteller" is the Prob too.


As tables "Problem" and "Hersteller" are n:n connected there is
a intersection
table called "Prob_Hers". I followed OfficeDev18 suggestion and
broke my
statement into small pieces.

I tried to do a LEFT OUTER JOIN in a query that would show me all rows
from "Problem" (ignoring if there is a "Hersteller" connected
to it) but only the rows from "Hersteller" that are connected to a
"Problem".

The main idea is to show all rows from "Problem" and if there is
additional info in other tables as "Hersteller" ("Version"
etc.) this should be shown too. If there is no data in "Herstller"
("Version" etc.) everything from Problem should still show up!

To begin with I did this:

SELECT p.Problem, p.Problem_lang, h.Hersteller
FROM (Problem As p LEFT JOIN Prob_Hers AS ph ON
p.ID_Problem=ph.ID_Problem) INNER JOIN Hersteller AS h ON
h.ID_Hersteller=ph.ID_Hersteller;

And Access tells me "Verknüpfungsausdruck nicht unterstützt." In
English that should be something like: The connecting
expression/operator is unsupported/wrong.

Am I doing a stupid mistake, or even some serious design error? My main
idea can't be so hard, can it?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using more than one join in a query 1
Several Extern Join in query 1
How to query with both JOIN and aliases in Access? 3
How to construct a join query, access ask for parameter ? 3
how to use where clause in my query 5
INNER JOIN table to query - need to delete query! 2
3-Way Query: Syntax Error (missing operator) in query expression 4
How to LEFT JOIN with 2 conditions fails in Access and .NET Query Builder? 3

Back
Top