Post reply

JOIN problems & confusions

  • July 14, 2003 at 10:33 am

    #82094

    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

    #465419

    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.

    

    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

    		AND	(

    			6 BETWEEN sm.SM_ACCESSLEVEL AND sm.SM_ACCESSLEVELOFF OR

    			sm.SM_ME_ID IS NULL

    		)

    WHERE	6 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

    #465549

    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