many-to-many query

[Emily Lena Jones]

Hi, I'm totally new at this so have no idea whether I'm asking for something
easy or quite difficult.

I am working in MySQL 3.23.58/PHP and am trying to construct a rather complex
query.  I have three tables: resources (containing resid, descr, title, url),
topicdir (containing topicid, resid) and topic (containing topicid,
topic, parentid).  Each resource is associated with numerous topics.
Initially I wanted to get just all the resources associated with a
particular topic (in this example #36), so had the following SQL statement:

SELECT resources.title, resources.descr, resources.url
FROM resources, topic_dir
WHERE topic_dir.topic_id = 36 AND topic_dir.res_id = resources.res_id
ORDER BY resources.title

Now it gets a bit more complicated: I need all resources associated
with topic 36 (or whatever) but also all topics with parentid 998
associated with the resources associated with topic 36.  Because I'm working in
an earlier version of MySQL, I can't use a subquery.

Any ideas/solutions/help would be most appreciated!

Many thanks,
Emily


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: many-to-many query

[Emily Lena Jones]

Quoting Rhino [EMAIL PROTECTED]:


 I wasn't sure if you understood the concept of association tables so forgive
 me if I told you things you already knew; I didn't mean to be patronizing.

No problem--you weren't patronizing at all, and I did tell you I was new at
this!

 With respect to your queries, it would be a lot easier to help if you showed
 us a little bit of the data from each table, the queries that you are
 running, the error messages (or incorrect results) you are getting and the
 results that you *wanted* to get. Otherwise, it is very hard to envision
 what you are trying to accomplish.


Okay, here's some more detail: what I'm working with is, essentially, a catalog
of web resources.  For each resource there is a title, a description, and a
URL, as well as a primary key.  That's the resources table (containing resid,
title, description, and url).

In addition, the resources are all associated with numerous categories of
different types (those are in the topic table, which has the following fields:
topicid, topic, parentid, and type).  The types of categories included are
topic (all of these have a parentid of NULL), subtopic (all of these have a
parentid equivalent to the topicid of their parent), resource type (all of
these have a parentid of 998), and a few others.  (I know this is a little
confusing--I've inherited this format, it's there for other reasons).

Then there's the topic_dir table, which is my association table linking
resources and topic tables.

What I'm trying to do is this: get the resource type of all resources with the
topicid of 36.  If I were working in Access, I would save a query on topicid,
and then use that to build the final query.  Is there a way to do this in
mySQL, or is there a better way to accomplish the same end?

Thanks!


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]