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]
How to call a stored procedure from a stored function with parameter?
Hello, I have the following stored procedure PROCEDURE `IsProductInForeignDatabase`(IN stock INTEGER(11), OUT MyResult VARCHAR(1000), IN Behaviour VARCHAR(20)) The procedure above returns the MyResult out parameter. I have a stored function as well thas has the body: Begin Declare MyResult VARCHAR(1000); set MyResult = ''; call `IsProductInForeignDatabase`(1, MyResult,'question'); RETURN 50; END; In the code above the return 50 is only a test: if I use the call statement in the body then the function will return nothing at all, on the contrary when I don't use the call statement, then it returns 50. The problem should be with the MyResult variable, but I don't know what to do. I tried with @ sign, with no difference. It is interesting that if I call the procedure within an Sql window it works perfectly like this: CALL `IsProductInForeignDatabase`(1, @w,'question'); SELECT @w; If it worked the end of the function would be return MyResult. Thank you in advance Zsolt Csillag www.egyediszoftverek.hu www.starsoft.hu
SELECT ... LIKE and the Korean character set
Hello. I have a question about the behavior of SELECT ... LIKE and dealing with the Korean language. For those who don't know anything about the way the language's characters are formulated, I'll give you a quick crash course: Korean has its own alphabet, just like Japanese, Chinese and most other Asian languages. However, each Korean character in a word is actually a syllable composed of those alphabets. So ㅎ (H) + ㅏ (A) + ㄴ (N) = 한. (If the mailing list doesn't support Unicode/UTF-8, please tell me so I can direct you to a better example.) But with the way Unicode works, it's not exactly like H is in the same block that HAN is represented in, especially in the Korean character table. So here's the issue I'm having: I'm writing a Korean dictionary for myself. Basically, an application for adding words I've learned in my Korean lessons. But I'm having a problem with the LIKE part of the SELECT statement. Essentially, I'd like to do the Korean equivalent of LIKE 'A%'. However, doing LIKE 'ㅎ%', when a word like 'hangul' (한글) is in the database, I get no results. Obviously, creating a table of every combination of every character possible for each consonant in the Korean language is nonoptimal. So what can I do to solve this problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restrict inserts to meet certain conditions?
Hi, Is it possible to restrict Inserts and Updates to meet certain conditions? In one of my tables lets say field A, B and C can be NULL, but not all at once. Depending on what type of item I want to store, A needs to be NULL, but B and C need values, for other types A needs a value but B and C can be NULL etc. Is it possible to implement this checking logic on the database level via CONSTRAINTs or something similar? Best Regards, Dav -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
