Difference between Access and SQL Server

[Beringer]

I have the following query in Access:

SELECT a.interest_pare nt_id, a.interest_elem ent_id, b.alr_category_ id AS
interest_catego ry_id, a.allergy_paren t_id, a.allergy_eleme nt_id,
c.alr_category_ id AS allergy_categor y_id
FROM (AllergyDrugPer mutation AS a INNER JOIN alr_category_dr ug_map AS b ON
a.interest_elem ent_id = b.drug_id) INNER JOIN alr_category_dr ug_map AS c ON
a.allergy_eleme nt_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_pare nt_id, a.interest_elem ent_id, b.alr_category_ id AS
interest_catego ry_id, a.allergy_paren t_id, a.allergy_eleme nt_id,
c.alr_category_ id AS allergy_categor y_id
FROM dbo.AllergyDrug Permutation a INNER JOIN
dbo.alr_categor y_drug_map b ON a.interest_elem ent_id =
b.drug_id INNER JOIN
dbo.alr_categor y_drug_map c ON a.allergy_eleme nt_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

[Beringer]

I think I found the problem. AllergyDrugPerm utation 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" <bo*********@in valid.com> wrote in message
news:mbSdd.5630 8$kz3.48760@fed 1read02...

I have the following query in Access:

SELECT a.interest_pare nt_id, a.interest_elem ent_id, b.alr_category_ id AS
interest_catego ry_id, a.allergy_paren t_id, a.allergy_eleme nt_id,
c.alr_category_ id AS allergy_categor y_id
FROM (AllergyDrugPer mutation AS a INNER JOIN alr_category_dr ug_map AS b ON
a.interest_elem ent_id = b.drug_id) INNER JOIN alr_category_dr ug_map AS c
ON a.allergy_eleme nt_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_pare nt_id, a.interest_elem ent_id, b.alr_category_ id
AS interest_catego ry_id, a.allergy_paren t_id, a.allergy_eleme nt_id,
c.alr_category_ id AS allergy_categor y_id
FROM dbo.AllergyDrug Permutation a INNER JOIN
dbo.alr_categor y_drug_map b ON a.interest_elem ent_id
= b.drug_id INNER JOIN
dbo.alr_categor y_drug_map c ON a.allergy_eleme nt_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

[debashish]

Did you get any error message when the query timeout occured?

[John Bell]

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" <bo*********@in valid.com> wrote in message
news:1mSdd.5630 9$kz3.53477@fed 1read02...

I think I found the problem. AllergyDrugPerm utation 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" <bo*********@in valid.com> wrote in message
news:mbSdd.5630 8$kz3.48760@fed 1read02...

I have the following query in Access:

SELECT a.interest_pare nt_id, a.interest_elem ent_id, b.alr_category_ id AS
interest_catego ry_id, a.allergy_paren t_id, a.allergy_eleme nt_id,
c.alr_category_ id AS allergy_categor y_id
FROM (AllergyDrugPer mutation AS a INNER JOIN alr_category_dr ug_map AS b ON a.interest_elem ent_id = b.drug_id) INNER JOIN alr_category_dr ug_map AS c
ON a.allergy_eleme nt_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_pare nt_id, a.interest_elem ent_id, b.alr_category_ id AS interest_catego ry_id, a.allergy_paren t_id, a.allergy_eleme nt_id,
c.alr_category_ id AS allergy_categor y_id
FROM dbo.AllergyDrug Permutation a INNER JOIN
dbo.alr_categor y_drug_map b ON a.interest_elem ent_id = b.drug_id INNER JOIN
dbo.alr_categor y_drug_map c ON a.allergy_eleme nt_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