Many-to-many query (chained)
Hi SQLers, I am searching for an SQL command to combine several many-to-many queries. At the end I only want to get results suitable for all restrictions. Better show you my tables: USER id name 1 frank ROLE id name 1 admin 2 general GROUP id name 1 groupA 2 groupB USER_ROLE user_id role_id 1 1 1 2 USER_GROUP user_id group_id 1 1 1 2 The query I am searching for should only process USERs that have ROLE 1 and 2 AND furthermore have GROUPs 1 and 2 at the same time. How to chain these restrictions into ONE SQL-query? What would the query look like? The following query would only get me all USERs having ROLE 1: SELECT * FROM USER WHERE id IN (SELECT DISTINCT USER_ROLE.user_id FROM USER_ROLE WHERE USER_ROLE.role_id = 1); Thanks in advance! I really need help with this! Thanks again. -- View this message in context: http://www.nabble.com/Many-to-many-query-%28chained%29-tf4539759.html#a12956571 Sent from the MySQL - General mailing list archive at Nabble.com. -- 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 (chained)
Rapthor, Try ... SELECT u.name FROM user u JOIN user_role ur ON u.id=ur.user_id AND (u.id=1 OR u.id=2) JOIN user_group ug ON u.id=ug.user_id AND (u.id=1 OR u.id=2) HAVING COUNT( DISTINCT ur.role_id ) = 2 AND COUNT( DISTINCT ug.group_id ) = 2; BTW you can't name a table 'group'; it's a reserved word. PB - Rapthor wrote: Hi SQLers, I am searching for an SQL command to combine several many-to-many queries. At the end I only want to get results suitable for all restrictions. Better show you my tables: USER id name 1 frank ROLE id name 1 admin 2 general GROUP id name 1 groupA 2 groupB USER_ROLE user_id role_id 1 1 1 2 USER_GROUP user_id group_id 1 1 1 2 The query I am searching for should only process USERs that have ROLE 1 and 2 AND furthermore have GROUPs 1 and 2 at the same time. How to chain these restrictions into ONE SQL-query? What would the query look like? The following query would only get me all USERs having ROLE 1: SELECT * FROM USER WHERE id IN (SELECT DISTINCT USER_ROLE.user_id FROM USER_ROLE WHERE USER_ROLE.role_id = 1); Thanks in advance! I really need help with this! Thanks again. -- 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 (chained)
Great! With a little adaption it works! Yes and of course I have to use another name for groups :) It was just an example. I have slightly different table settings. SELECT u.name FROM user u JOIN user_role ur ON u.id=ur.user_id AND (ur.role_id=1 OR ur.role_id=2) JOIN user_group ug ON u.id=ug.user_id AND (ug.group_id=1 OR ug.group_id=2) HAVING COUNT( DISTINCT ur.role_id ) = 2 AND COUNT( DISTINCT ug.group_id ) = 2 GROUP BY name; Thanks! You really saved my day! pzbrawl wrote: Rapthor, Try ... SELECT u.name FROM user u JOIN user_role ur ON u.id=ur.user_id AND (u.id=1 OR u.id=2) JOIN user_group ug ON u.id=ug.user_id AND (u.id=1 OR u.id=2) HAVING COUNT( DISTINCT ur.role_id ) = 2 AND COUNT( DISTINCT ug.group_id ) = 2; BTW you can't name a table 'group'; it's a reserved word. PB - Rapthor wrote: Hi SQLers, I am searching for an SQL command to combine several many-to-many queries. At the end I only want to get results suitable for all restrictions. Better show you my tables: USER id name 1 frank ROLE id name 1 admin 2 general GROUP id name 1 groupA 2 groupB USER_ROLE user_id role_id 1 1 1 2 USER_GROUP user_id group_id 1 1 1 2 The query I am searching for should only process USERs that have ROLE 1 and 2 AND furthermore have GROUPs 1 and 2 at the same time. How to chain these restrictions into ONE SQL-query? What would the query look like? The following query would only get me all USERs having ROLE 1: SELECT * FROM USER WHERE id IN (SELECT DISTINCT USER_ROLE.user_id FROM USER_ROLE WHERE USER_ROLE.role_id = 1); Thanks in advance! I really need help with this! Thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Many-to-many-query-%28chained%29-tf4539759.html#a12959127 Sent from the MySQL - General mailing list archive at Nabble.com. -- 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
- 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]
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! Many thanks, Emily -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
many-to-many query
http://dev.mysql.com/doc/mysql/en/JOIN.html -Original Message- From: Emily Lena Jones [mailto:[EMAIL PROTECTED] Sent: Thursday, October 28, 2004 1:05 PM To: [EMAIL PROTECTED] 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! 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
- 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_idlastname... 1Smith 2Jones 3Black 4Green Project proj_idprojname A0001Mortgage System B0002Payroll System C0003Warehouse System D0004Finance System Emp_Proj --- emp_idproj_id 1A0001 2C0003 3A0001 4B0002 2B0002 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]
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? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
