Help trying to limit the results on a 3-table query?

[mmitchell_houston]

I'm working on a .NET project and I need a single query to return a
result set from three related tables in Access 2003, and I'm having
trouble getting the results I want.

The details:

Question ------------
QuestionID
QuestionText
Question_MediaTypeID
etc.

Answer --------------
AnswerID
Answer_QuestionID
AnswerOptionText
etc.
AnswerMarket -----------
AMID
AM_MediaTypeID
AMmediaContent
etc.

The Question and Answer table are joined by the QuestionID and
Answer_QuestionID columns.
The Question and AnswerMarket tables are joined by the
Question_MediaTypeID and AM_MediaTypeID (they are both referring to
another table named MediaType, which describes whether we're talking
about newspapers, websites, or radio).

I need to pull back ONE specific record from the Question table (based
on the QuestionOrder parameter passed through a URL). No problem.

I need to pull back the THREE associated records in the Answer table:

SELECT *
FROM Question, Answer
WHERE QuestionOrder = @QuestionOrder AND
QuestionID = Answer_QuestionID

Works like a charm.

Now, there are EIGHTEEN rows in the AnswerMarket table. And, of
course, when I run this query I get 54 rows returned (3 x 18 = 54):

SELECT *
FROM Question, Answer, AnswerMarket_Chicago
WHERE
QuestionOrder = @QuestionOrder AND
QuestionID = Answer_QuestionID AND
Question_MediaTypeID = AM_MediaTypeID
I've tried a few techniques (including subqueries and different join
types), but I'm not getting the results I need.

Any suggestions and guidance (icluding best practices for this type of
situation) would be greatly appreciated.

[GH]

If you are writing this query for Access or SQL Server, you might be
better off writing the query using the INNER JOIN syntax rather than
comma joins, as it is much easier to inadvertantly create a Cartesian
join using the latter. In your example, a JOIN query might look more
like:

SELECT * (<-- You really should itemize your select list rather than
using the wildcard.)
FROM (Question INNER JOIN Answer ON QuestionID = Answer_QuestionID)
INNER JOIN AnswerMarket_Chicago ON Question_MediaTypeID =
AM_MediaTypeID)
WHERE QuestionOrder = @QuestionOrder

The above join selects the answers for the selected question then the
media types for the question/answer records returned.

By the way, I am not sure why you are getting Cartesian results. I
duplicated your table structure and added some records, and I still get
back the correct result set with the query as you have written it.

- GH
mmitchell_houston wrote:

I'm working on a .NET project and I need a single query to return a
result set from three related tables in Access 2003, and I'm having
trouble getting the results I want.

The details:

Question ------------
QuestionID
QuestionText
Question_MediaTypeID
etc.

Answer --------------
AnswerID
Answer_QuestionID
AnswerOptionText
etc.
AnswerMarket -----------
AMID
AM_MediaTypeID
AMmediaContent
etc.

The Question and Answer table are joined by the QuestionID and
Answer_QuestionID columns.
The Question and AnswerMarket tables are joined by the
Question_MediaTypeID and AM_MediaTypeID (they are both referring to
another table named MediaType, which describes whether we're talking
about newspapers, websites, or radio).

I need to pull back ONE specific record from the Question table (based
on the QuestionOrder parameter passed through a URL). No problem.

I need to pull back the THREE associated records in the Answer table:

SELECT *
FROM Question, Answer
WHERE QuestionOrder = @QuestionOrder AND
QuestionID = Answer_QuestionID

Works like a charm.

Now, there are EIGHTEEN rows in the AnswerMarket table. And, of
course, when I run this query I get 54 rows returned (3 x 18 = 54):

SELECT *
FROM Question, Answer, AnswerMarket_Chicago
WHERE
QuestionOrder = @QuestionOrder AND
QuestionID = Answer_QuestionID AND
Question_MediaTypeID = AM_MediaTypeID
I've tried a few techniques (including subqueries and different join
types), but I'm not getting the results I need.

Any suggestions and guidance (icluding best practices for this type of
situation) would be greatly appreciated.

[Tim Marshall]

mmitchell_houston wrote:

I'm working on a .NET project and I need a single query to return a
result set from three related tables in Access 2003, and I'm having
trouble getting the results I want.

If this is my buddy from a place caled TMP, you're using the wrong join
syntax. 8)

Use the Access query builder... it's easy and quick and you dont have to
make your brain do somersaults figuring out how the from syntax goes...
You can copy the SQL from the query builder and use it in VBA or
whatever .net code you're writing.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me