Difference between Access and SQL Server

I have the following query in Access:

SELECT a.interest_parent_id, a.interest_element_id, b.alr_category_id AS
interest_category_id, a.allergy_parent_id, a.allergy_element_id,
c.alr_category_id AS allergy_category_id
FROM (AllergyDrugPermutation AS a INNER JOIN alr_category_drug_map AS b ON
a.interest_element_id = b.drug_id) INNER JOIN alr_category_drug_map AS c ON
a.allergy_element_id = c.drug_id

and all is well!

When I create the same query in SQL sever (using the desinger), I get:

SELECT a.interest_parent_id, a.interest_element_id, b.alr_category_id AS
interest_category_id, a.allergy_parent_id, a.allergy_element_id,
c.alr_category_id AS allergy_category_id
FROM dbo.AllergyDrugPermutation a INNER JOIN
dbo.alr_category_drug_map b ON a.interest_element_id =
b.drug_id INNER JOIN
dbo.alr_category_drug_map c ON a.allergy_element_id =
c.drug_id

The only structural difference I see here is that SQL removed the ( and )
from around the first join and created the double join.
When I run this query I get stuck in a loop and eventually the sever will
time out. If I only have the first join there is no time out issues but of
course the result is not what I desire.

Can someone help me out here and tell me what is wrong with the statement
used in SQL?

Thank you in advance,
EricI think I found the problem. AllergyDrugPermutation is a "view." If I
create a table the has the same data as that in the view I don't get the
time out error.
So the question becomes, why does this fail with view?
Thanks
Eric

"Beringer" <borden_eric@.invalid.com> wrote in message
news:mbSdd.56308$kz3.48760@.fed1read02...
>I have the following query in Access:
> SELECT a.interest_parent_id, a.interest_element_id, b.alr_category_id AS
> interest_category_id, a.allergy_parent_id, a.allergy_element_id,
> c.alr_category_id AS allergy_category_id
> FROM (AllergyDrugPermutation AS a INNER JOIN alr_category_drug_map AS b ON
> a.interest_element_id = b.drug_id) INNER JOIN alr_category_drug_map AS c
> ON a.allergy_element_id = c.drug_id
> and all is well!
> When I create the same query in SQL sever (using the desinger), I get:
> SELECT a.interest_parent_id, a.interest_element_id, b.alr_category_id
> AS interest_category_id, a.allergy_parent_id, a.allergy_element_id,
> c.alr_category_id AS allergy_category_id
> FROM dbo.AllergyDrugPermutation a INNER JOIN
> dbo.alr_category_drug_map b ON a.interest_element_id
> = b.drug_id INNER JOIN
> dbo.alr_category_drug_map c ON a.allergy_element_id =
> c.drug_id
> The only structural difference I see here is that SQL removed the ( and )
> from around the first join and created the double join.
> When I run this query I get stuck in a loop and eventually the sever will
> time out. If I only have the first join there is no time out issues but
> of course the result is not what I desire.
> Can someone help me out here and tell me what is wrong with the statement
> used in SQL?
> Thank you in advance,
> Eric|||Did you get any error message when the query timeout occured?|||Hi

You would need to post DDL and example data (as insert statements) that
recreates the problem
http://www.aspfaq.com/etiquette.asp?id=5006

If you posted the access query (from the access designer) into query
analyser do you still have a problem?

John

"Beringer" <borden_eric@.invalid.com> wrote in message
news:1mSdd.56309$kz3.53477@.fed1read02...
> I think I found the problem. AllergyDrugPermutation is a "view." If I
> create a table the has the same data as that in the view I don't get the
> time out error.
> So the question becomes, why does this fail with view?
> Thanks
> Eric
> "Beringer" <borden_eric@.invalid.com> wrote in message
> news:mbSdd.56308$kz3.48760@.fed1read02...
> >I have the following query in Access:
> > SELECT a.interest_parent_id, a.interest_element_id, b.alr_category_id AS
> > interest_category_id, a.allergy_parent_id, a.allergy_element_id,
> > c.alr_category_id AS allergy_category_id
> > FROM (AllergyDrugPermutation AS a INNER JOIN alr_category_drug_map AS b
ON
> > a.interest_element_id = b.drug_id) INNER JOIN alr_category_drug_map AS c
> > ON a.allergy_element_id = c.drug_id
> > and all is well!
> > When I create the same query in SQL sever (using the desinger), I get:
> > SELECT a.interest_parent_id, a.interest_element_id,
b.alr_category_id
> > AS interest_category_id, a.allergy_parent_id, a.allergy_element_id,
> > c.alr_category_id AS allergy_category_id
> > FROM dbo.AllergyDrugPermutation a INNER JOIN
> > dbo.alr_category_drug_map b ON
a.interest_element_id
> > = b.drug_id INNER JOIN
> > dbo.alr_category_drug_map c ON a.allergy_element_id
=
> > c.drug_id
> > The only structural difference I see here is that SQL removed the (
and )
> > from around the first join and created the double join.
> > When I run this query I get stuck in a loop and eventually the sever
will
> > time out. If I only have the first join there is no time out issues but
> > of course the result is not what I desire.
> > Can someone help me out here and tell me what is wrong with the
statement
> > used in SQL?
> > Thank you in advance,
> > Eric