----- Original Message ----- From: "Emily Lena Jones" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, October 28, 2004 7:46 PM Subject: Re: many-to-many query
> > 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? > I'm one of those people who benefit a great deal from a "picture", e.g. a little bit of sample data, to help me visualize the data and the relationships between them. You haven't provided that nor a clear description of just what errors or incorrect results you are getting so I really can't suggest anything concrete other than one thing: a sample three table join. (Perhaps you aren't clear on how the syntax looks for a three table join; I've never seen an example of one in the manual.) Given three tables, TabA, TabB, and TabC, that need to be joined together: select a.col2, a.col3, c.col1, c.col2, b.col4 from TabA a inner join TabC c on a.col1=c.col2 inner join TabB b on b.col3=a.col2 where [whatever] group by [whatever] having [whatever] order by [whatever] The second and third lines of this example are critical since they define which tables are being joined together on what conditions: TabA is being inner-joined to TabC where the value in col1 of TabA is the same as the value in col2 of TabC; TabB is being inner-joined to the result of the previous join where TabB's col3 value is the same as TabA's col2 value. Of course you could have joined the tables in any convenient order and on any columns that they had in common; this is just an arbitrary example. The main thing is that the first join has a table name (and alias) on either side of the 'inner join' phrase but the subsequent joins do not: they simply say 'inner join' (or 'straight join' or 'outer join' or 'cross join' or ...) and the next table name. Each join is followed by an 'on' clause that defines the columns that are the basis of the join. (The columns that are the basis of the join *CAN* be put in the WHERE clause instead but I personally prefer to do my joining in 'on' clauses and put only 'local' conditions, conditions that affect only one of the tables, in the the WHERE clause.) Does this help? If not, please provide more information on precisely what isn't working and what result you expected as well as a small sampling of your data so that we can help more. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
