Help on DBRW custom query with 2 INNER JOIN statements

[Guest]

Hi everyone,
Can somebody please help me with the problem below?

I want to write a custom query in the Database Results Wizard (DBRW) with 2
INNER JOIN statements, but I keep on getting a syntax error. I hope one of
you can tell me what I'm doing wrong.

I want to write the query (for a database with 3 tables called Houses,
Prop_price, Neighborh):

SELECT * FROM Houses INNER JOIN Prop_price ON Houses.id =
Prop_price.house_id INNER JOIN Neighborh ON Houses.nbh_id = Neighborh.nbh_id
WHERE( Prop_price.curr LIKE '::currency::' AND Prop_price.rent_sale LIKE
'::rent_sale::' AND Prop_price.price >= ::startprice:: AND Prop_price.price
<= ::endprice:: AND Houses.hs_apt LIKE '::hs_apt::' AND Neighborh.neighbourh
LIKE '::neighbourh::')

And I get the following error:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'Houses.id = Prop_price.house_id INNER JOIN Neighborh ON
Houses.nbh_id = Neighborh.nbh_id'.

However if I write the following query with 1 INNER JOIN statement (and the
2 tables Houses, Prop_price) the DBRW works just fine:

SELECT * FROM Houses INNER JOIN Prop_price ON Houses.id =
Prop_price.house_id WHERE( Prop_price.curr LIKE '::currency::' AND
Prop_price.rent_sale LIKE '::rent_sale::' AND Prop_price.price >=
::startprice:: AND Prop_price.price <= ::endprice:: AND Houses.hs_apt LIKE
'::hs_apt::')

Can anyone tell me what i do wrong when I use 2 INNER JOIN statements?
By the way I use ASP to display the information from my database.

Thanks

Margot

 

[Guest]

Ok, I've figured it out myself. Have to put brackets after the FROM and
around the 1st INNER JOIN statement so that the DBRW doesn't confuse the two
statements. The code below works.

SELECT * FROM (Houses INNER JOIN Prop_price ON Houses.id =
Prop_price.house_id) INNER JOIN Neighborh ON Houses.nbh_id = Neighborh.nbh_id
WHERE( Prop_price.curr LIKE '::currency::' AND Prop_price.rent_sale LIKE
'::rent_sale::' AND Prop_price.price >= ::startprice:: AND Prop_price.price
<= ::endprice:: AND Houses.hs_apt LIKE '::hs_apt::' AND Neighborh.neighbourh
LIKE '::neighbourh::')

Margot