JOIN problems & confusions

  • 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

  • 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___

  • 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