Help with Parameter Query

[Dave]

I have a database that has articles that are classified by topic. The
article and topic are a many to many relationship that is implemented
through the junction table articletopic.

I wrote a query that will return all of the articles that are related to 2
specified topics (e.g., give me all of the articles that are related to
topicid 107 AND topicid 87).

This was a more difficult query to write than I first realized but the
following syntax gives me what I need...

SELECT Article.date2
,Topic.topic
,Topic.topicid
,Article.articleid
,Article.title
,Article.abstract
,(SELECT note FROM articletopic WHERE articleid=x.articleid AND
topicid=x.topicid) AS note_x
,(SELECT note FROM articletopic WHERE articleid=y.articleid AND
topicid=y.topicid) AS note_y
FROM (([SELECT atp.articleid, atp.topicid FROM articletopic atp WHERE
atp.topicid = 87]. AS x
INNER JOIN [SELECT atp2.articleid, atp2.topicid FROM articletopic atp2
WHERE atp2.topicid = 107]. AS y ON x.articleid = y.articleid)
INNER JOIN Article ON x.articleid = Article.ArticleID)
INNER JOIN Topic ON x.topicid = Topic.TopicID
ORDER BY date2 DESC;

My problem is that I cannot paramatize thuis query.

Notice that the topic ids are hard-coded in the two derived tables I use to
get my two topic sets. But I need them to be parameters so they can be
changed on the fly.

If I try to write the derived tables like this...

SELECT atp.articleid, atp.topicid FROM articletopic atp WHERE atp.topicid
= [p1]

... I get a syntax a error in JOIN operation.

This is noit a problem with a SQL Server stored procedure but can anyone
give me an idead on how to do it in Access 2003?

Thanks
Dave

 

[Michael Gramelspacher]

I have a database that has articles that are classified by topic. The
article and topic are a many to many relationship that is implemented
through the junction table articletopic.

I wrote a query that will return all of the articles that are related to 2
specified topics (e.g., give me all of the articles that are related to
topicid 107 AND topicid 87).

This was a more difficult query to write than I first realized but the
following syntax gives me what I need...

SELECT Article.date2
,Topic.topic
,Topic.topicid
,Article.articleid
,Article.title
,Article.abstract
,(SELECT note FROM articletopic WHERE articleid=x.articleid AND
topicid=x.topicid) AS note_x
,(SELECT note FROM articletopic WHERE articleid=y.articleid AND
topicid=y.topicid) AS note_y
FROM (([SELECT atp.articleid, atp.topicid FROM articletopic atp WHERE
atp.topicid = 87]. AS x
INNER JOIN [SELECT atp2.articleid, atp2.topicid FROM articletopic atp2
WHERE atp2.topicid = 107]. AS y ON x.articleid = y.articleid)
INNER JOIN Article ON x.articleid = Article.ArticleID)
INNER JOIN Topic ON x.topicid = Topic.TopicID
ORDER BY date2 DESC;

My problem is that I cannot paramatize thuis query.

Notice that the topic ids are hard-coded in the two derived tables I use to
get my two topic sets. But I need them to be parameters so they can be
changed on the fly.

If I try to write the derived tables like this...

SELECT atp.articleid, atp.topicid FROM articletopic atp WHERE atp.topicid
= [p1]

.. I get a syntax a error in JOIN operation.

This is noit a problem with a SQL Server stored procedure but can anyone
give me an idead on how to do it in Access 2003?

Thanks
Dave

This code is from Jamie Collins. It is a saved query named LoadList. It
requires a Sequence table of consecutive numbers (0,1,2,3...100)

PARAMETERS delimted_text Text ( 255 ), delimiter Text ( 255 );
SELECT MID(I1.input_string,S1.seq,MIN(S2.seq) - S1.seq - LEN(delimiter)) AS
param
FROM [SELECT DISTINCT delimted_text AS input_string
FROM SEQUENCE AS S3]. AS I1, SEQUENCE AS S1, SEQUENCE AS S2
WHERE MID(delimiter & I1.input_string & delimiter,S1.seq,
LEN(delimiter)) = delimiter
AND MID(delimiter & I1.input_string &
delimiter,S2.seq,
LEN(delimiter)) = delimiter
AND S1.seq < S2.seq
AND S1.seq BETWEEN 1
AND LEN(delimiter) + LEN(delimted_text) +
LEN(delimiter)
AND S2.seq BETWEEN 1
AND LEN(delimiter) + LEN(delimted_text) +
LEN(delimiter)
GROUP BY I1.input_string, S1.seq
HAVING LEN(MID(I1.input_string,S1.seq,MAX(S2.seq) - S1.seq - LEN(delimiter))) >
0;

This is from Joe Celko's article on searching keywords.
http://www.dbazine.com/ofinterest/oi-articles/celko9

My keyword list is 200,000 words taken from 4,000 articles. This query takes
several seconds to run. It is slow. I do not use Jamie Collins' query. I used
a form and write the keywords to a SearchList table and use that. Run time is
close to instantaneous. Actually, keyword searching is too limited for me. I
use a wildcard search against the articles themselves, and that is also nearly
instantaneous.

SELECT *
FROM Obituaries
WHERE ObituaryID IN
(SELECT K1.RecordNbr FROM Keywords AS K1, LoadList AS S1 WHERE
K1.Keyword=S1.param
GROUP BY K1.RecordNbr
HAVING COUNT (K1.Keyword) >=(SELECT COUNT(param) FROM loadlist))
ORDER BY LastName, FirstName;