July 14, 2003 at 10:33 am
I have a problem with 2 tables which I normally do a LEFT OUTER JOIN ON
The "left" table is the Main Menu table, the "right" table is the Sub-Menu Table...
That's not normally a problem, but we've recently added a Security Level option to the Menus and Sub-Menus tables
Now, the issue I have is that if a Menu has one Sub-Menu, but the Menu is of a security level that is visible to the user, but the Sub-Menu isn't, then my current query "hides" the Menu item...
Any ideas on how I can make this work with one SQL statement?
Here's my current query:
SELECT me.ME_ID, me.ME_DESCRIPTION, me.ME_PAGELINK, sm.SM_DESCRIPTION, sm.SM_PAGELINK, sm.SM_ME_ID
FROM MENULINKS me LEFT OUTER JOIN
SUBMENULINKS sm ON me.ME_ID = sm.SM_ME_ID
WHERE (6 BETWEEN me.ME_ACCESSLEVEL AND me.ME_ACCESSLEVELOFF) AND (sm.SM_ME_ID IS NULL) OR
(6 BETWEEN me.ME_ACCESSLEVEL AND me.ME_ACCESSLEVELOFF) AND (6 BETWEEN sm.SM_ACCESSLEVEL AND sm.SM_ACCESSLEVELOFF)
ORDER BY me.ME_SORT, sm.SM_SORT
The "6" is the current user level, the ACCESSLEVEL and ACCESSLEVELOFF are the "on" and "off" security levels
Any help would be greatly appreciated 🙂
Thanks in advance
July 14, 2003 at 12:58 pm
Hi WebPhil!
From what I've understood you want the menuitem that only have one underlying menyitem where the user have access to the menuitem but not the underlying menuitem, to show up in your select with null-values like menuitems do that not have any underlying menuitems? If I've got the question correct, this code might just work for you.
SELECTme.ME_ID,
me.ME_DESCRIPTION,
me.ME_PAGELINK,
sm.SM_DESCRIPTION,
sm.SM_PAGELINK,
sm.SM_ME_ID
FROMMENULINKS me
LEFT OUTER JOIN SUBMENULINKS sm ON me.ME_ID = sm.SM_ME_ID
AND(
6 BETWEEN sm.SM_ACCESSLEVEL AND sm.SM_ACCESSLEVELOFF OR
sm.SM_ME_ID IS NULL
)
WHERE6 BETWEEN me.ME_ACCESSLEVEL AND me.ME_ACCESSLEVELOFF
ORDER BY
me.ME_SORT,
sm.SM_SORT
Best of luck,
- Robin
robbac
Edited by - robbac on 07/14/2003 12:59:49 PM
robbac
___the truth is out there___
July 15, 2003 at 8:24 am
That is PERFECT
I don't know why I didn't think to put that in the FROM... I tend to think in WHERE's 🙂
Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply