----- Original Message ----- From: "Emily Lena Jones" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 28, 2004 4:05 PM Subject: many-to-many query
> 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! > MySQL is perfectly capable of joining together three or more tables in a single query, so rest assured that this is not a problem. I see that someone else, 'none none', has just pointed you to the article about 'join' in the MySQL manual. However, you may have an issue of database design to confront. I can't tell from your question whether you have actually designed your tables to implement true many-to-many relationships. If you have, I would strongly recommend that you think very hard before proceeding any further. The approach that almost every database professional uses is to break each many-to-many relationship into two one-to-many relationships via an association table. For instance, given a many-to-many relationship between employees and projects - an employee can be working on multiple projects and each project can involve many employees - the normal way to design the tables that describe this relationship is: create table employee (emp_id int not null, lastname char(20) not null, [etc.] primary key(emp_id)) Type=InnoDB; create table project (proj_id char(5) not null, projname char(20) not null, [etc.] primary key(proj_id)) Type=InnoDB; create table emp_proj (emp_id int not null, proj_id char(5) not null, primary key (emp_id, proj_id) foreign key(emp_id) references employee(emp_id), foreign key(proj_id) references project(proj_id)) Type=InnoDB; The resulting tables would look like this when populated with data: Employee ----------- emp_id lastname ... 1 Smith 2 Jones 3 Black 4 Green Project -------- proj_id projname A0001 Mortgage System B0002 Payroll System C0003 Warehouse System D0004 Finance System Emp_Proj ----------- emp_id proj_id 1 A0001 2 C0003 3 A0001 4 B0002 2 B0002 Therefore, employee 1 is working on Project A0001, employee 2 is working on Projects C0003 and B0002, employee 3 is working on Project A0001, and employee 4 is working on Project B0002. We can also infer that no one is working on Project D0004 because no employees in the Emp_proj table are recorded as being working on that project. If the tables are sufficiently small, you may know the names associated with the employee and project IDs off by heart, in which case you don't need to look at any table other than Emp_proj to determine which employees are on which projects or vice versa. If the volumes of data are too large to memorize the IDs, you can do joins between the Emp_proj, Employee, and Project tables to combine all of this data. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
