JOIN 3 Tables

[Christian Schlemmer]

Hi.

Want to join three tables as described in the MSAccess-Help, but I get an
Syntax-Error.

SELECT lastminute.last_id, lastminute.last_hotel_ID,
lastminute.last_stadt_id, lastminute.last_region_id FROM lastminute INNER
JOIN (hotels INNER JOIN (stadt INNER JOIN (region ON region.region_ID =
lastminute.last_region_id) ON stadt.stadt_ID = lastminute.last_stadt_id) ON
hotels.hotel_ID = lastminute.last_hotel_id) ORDER BY hotels.hotel_name DESC

Thank you.

Christian

 

[Guest]

You don't have joins for most of your tables. All of them are joined back to
something in lastminute. A JOIN condition has to be stated for each pair of
tables in the JOIN. For example, you have a JOIN between lastminute and
hotels (ON hotels.hotel_ID = lastminute.last_hotel_id) but you have none
between stadt and region.

I'm not sure that this would trigger a syntax error but it is certainly a
logical and design error. Do your relations show that there is a relationship
between tthe pairs of tables?

 

[Michel Walsh]

Hi,


The syntax is wrong, the ordering of the definition seems wrong too. As
example, the inner most join:

stadt INNER JOIN (region ON region.region_ID = lastminute.last_region_id)


has wrong ( ). Indeed, if we evaluate the ( ) first:


region ON region.region_ID = lastminute.last_region_id


makes no sense. Furthermore, at that level, we haven't defined, yet, what is
lastminute. The join seems to involve stadt and region, but the ON clause
involves other tables (and since we are at the innermost level of the join
definition, so we are limited to only these two tables, then).

Try redefining your joins with the query designer, or start a new query with
the query designer, and define your joins graphically.



Alternatively, with the information that you need:

ON region.region_ID = lastminute.last_region_id

ON stadt.stadt_ID = lastminute.last_stadt_id

ON hotels.hotel_ID = lastminute.last_hotel_id


three joins are needed, and all involve lastminute. So, make the inner join
implying lastminute and any other table, with its ON clause:


region INNER JOIN lastMinute ON region ... lastminute

then, add another table, with its join

( region INNER JOIN lastMinute ON ... ) INNER JOIN stadt ON statd...
lastMinute

and then, add the last table

((region INNER JOIN lastMinute ON ... ) INNER JOIN stadt ON ... ) INNER
JOIN hotels ON hotels... lastMinute




Hoping it may help,
Vanderghast, Access MVP

 

[Christian Schlemmer]

Hi,

thx for help.

SELECT * FROM ((region INNER JOIN lastminute ON region.region_ID =
lastminute.last_region_id) INNER JOIN stadt ON stadt.stadt_ID =
lastminute.last_stadt_id) INNER JOIN hotels ON hotels.hotel_ID =
lastminute.last_hotel_id ORDER BY lastminute.last_id DESC

Christian