Hello all:



I have two tables:



1. MenuAccess:



accessId

fkMenuId

fkGroupid

View

Execute



2. MenuOptions



MenuId

MenuName



I would like to get all of the records in MenuOptions and any record in
MenuAccess with a specific fkGroupid. For example:



A. MenuAccess



AccessId fkMenuID fkgroupid view execute

1 1 2 1
1

2 2 2 1
0

3 3 2 0
1

4 1 1 1
1



B. MenuOptions



Menuid MenuName

1 Order Maintenance

2 Customer Maintenance

3 Reports







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





Any will be appreciated.



Thanks

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.