Re: Need help with a sql query
Imran wrote: Hello all: ... I would like to get all of the records in MenuOptions and any record in MenuAccess with a specific fkGroupid. For example: ... IIf I run a query for fkgroupid = 1 I should get AccessId fkMenuID fkgroupid view execute MenuId MenuName 1 1 2 1 1 1 Order Maintenence Null Null Null Null Null 2 Customer Maintenence Null Null Null Null Null 3 Reports You'll need a UNION for: SELECT ma.AccessId, ma.fkMenuID, ma.fkgroupid, ma.view, ma.execute, mo.MenuId, mo.MenuName FROM MenuAccess AS ma LEFT JOIN MenuOptions AS mo ON mo.Menuid = ma.fkMenuID WHERE ma.fkgroupid = 1 UNION SELECT NULL, NULL, NULL, NULL, NULL, MenuID, MenuName FROM MenuOptions WHERE MenuID NOT IN (SELECT fkMenuID FROM MenuAccess WHERE fkgroupid = 1); (untested but it looks about right) Note that the second WHERE clause has a subquery where you have to repeat the given fkgroupid. Otherwise, you'll get 2 rows for Order Maintenence. I'm sure there's a much more elegant way to achieve this. As an aside, you really should pick one column-naming convention and stick to it. The case of the column names is all over the place (fkMenuID, fkgroupid, MenuID). This can only cause you headaches down the road when you're trying to remember if it was MenuID, menuId, menuid, or something altogether different. Personally, I use fk_menu_id. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help on a SQL query: need LEFT JOIN?
You wrote: I apologize for asking such a basic SQL question, but I am failing in finding the solution... Consider the following table: id | option --- 1 | a 2 | a 2 | b I want to find the ids with only a specific set of options. For example, if I wanted to get the ids which have option a only, the query should give me 1 as a result, not 1 and 2. I suspect I need to use a LEFT JOIN on the same table, but I have failed miserably so far... Many thanks in advance to the SQL experts ;-). Jean-Luc (http://jfontain.free.fr/moodss/) There are no need to use the left join in this query. The best way to do what you wanna is within the following query: SELECT id FROM table GROUP BY id HAVING COUNT(option) = 1; You can change the count value to take the ids that have specified number of options. Luís Fernando - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: need help on a SQL query: need LEFT JOIN?
I wrote: I apologize for asking such a basic SQL question, but I am failing in finding the solution... Consider the following table: id | option --- 1 | a 2 | a 2 | b I want to find the ids with only a specific set of options. For example, if I wanted to get the ids which have option a only, the query should give me 1 as a result, not 1 and 2. I suspect I need to use a LEFT JOIN on the same table, but I have failed miserably so far... Many thanks in advance to the SQL experts ;-). Thank you very much to all for your responses: it led me to the solution and in the process improved my SQL level: select id from t left join t as u on (t.id = u.id) and (t.option = u.option) and ( (t.option = 'a') or (t.option = 'b') or ... ) group by t.id having (count(*) = count(u.id)) and (count(*) = N) * with N = number of options to match Thanks again! Jean-Luc (http://jfontain.free.fr/moodss/) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: need help on a SQL query: need LEFT JOIN?
Does this work? select t.id,t.option,t2.option from tableName t left join tableName t2 on t.id=t2.id and t2.option!=t.option where t.option=a and isnull(t2.option); HTH, Jed On the threshold of genius, [EMAIL PROTECTED] wrote: I apologize for asking such a basic SQL question, but I am failing in finding the solution... Consider the following table: id | option --- 1 | a 2 | a 2 | b I want to find the ids with only a specific set of options. For example, if I wanted to get the ids which have option a only, the query should give me 1 as a result, not 1 and 2. I suspect I need to use a LEFT JOIN on the same table, but I have failed miserably so far... Many thanks in advance to the SQL experts ;-). Jean-Luc (http://jfontain.free.fr/moodss/) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
