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]
